When you're building formulas in Excel, it's common to reference cells that are on different worksheets, or even in different workbooks.
This easy to do.
Here we have 5 weeks of test scores for a group of students, and a summary sheet that's been set up to hold the test scores for all 5 weeks.
What we need to do is create formulas on the summary sheet that pull in the correct values from the other sheets.
Referencing cells on other sheets is very simlar to referencing cells on the same sheet. Start with an equal sign, then move to the sheet you want to reference.
In this case, we're just going to build a simple link to the scores in Week 1. Note that once we click a cell in Week 1, Excel shows the sheet name in the formula along with the cell address.
Sheet names always appear with an exclamation mark at the end, before the cell refeference. To complete the formula, there's no need to go back to the original sheet - just press enter.
Now let's copy this formula down. You can see that our formula is picking up all test scores in Week 1.
You might wonder what happens to the formula if we change the sheet name?
Luckily, this is not something you need to worry about. If I temporarily change the name, Excel simply adjusts the formulas.
I'll change it back now.
For week two, let's bring in the references a different way, with Paste special.
After I copy the scores to the clipboard, I can navigate back to the summary sheet and use Paste Special > Paste Link. Now we have a full set of formulas that reference test scores in Week2.
For week 3, I'll just type the formula in manually, beginning with the Week3 and and exclamation mark. I know that the first cell reference is D6 and because all sheets have the identical structure, I can just copy the formula down.
I'll do the same thing for Weeks 4 and 5.
I get an error with week 5 because the name actually has a space in it, and Excel can't find the sheet. Even when I add the name with the space I get an error. The second error is due to the space.
If a sheet name contains a space, you need to enclose the name in single quotes for the reference to be valid. Once I add the single quotes, I can enter the formula.