Let’s take a look. Here we have a workbook with five weeks of test scores for a group of students, and a summary sheet that’s been set up to hold test scores for all five 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 similar to referencing cells on the same sheet. Start with an equal sign, and 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 Week1. Note that once we click a cell in Week1, Excel shows the sheet name in the formula along with the cell address. Sheet names always appear with an exclamation point at the end, before the cell reference. 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 the formula is picking up all the test scores in Week1. 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 Week2, let’s bring in the reference in a different way with Paste Special. After I copy the scores for Week2 to the clipboard, I navigate back to the Summary sheet and use Paste Special > Paste Link. Now we have a full set of formulas that reference the test scores in Week2. For Week3, I’ll just type the formula in manually, beginning with Week3 and an exclamation mark. I know that the first cell reference is D6 because all sheets have the identical structure. Now 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 contains a space, and Excel can’t find that sheet. Even if I add the name with the space, I still get an error; but the second error is actually because of the space. When a sheet name contains a space, you’ll need to enclose the name in single quotes in order for the reference to be valid. Once I add the single quotes, I can enter the formula.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
