In this case, you could use a special trick called a “3D reference”.
Here are the test scores we looked at earlier.
The Summary sheet is pulling in the results from Week1 through Week5.
Suppose we want to average those test scores?
3D referencing works like a cell rangeyou use a colon between the starting and the ending sheets.
Once I enter the formula and copy it down, we get exactly the same averages we calculated earlier.
But in this case, the formula does not depend on the values in the Summary sheet.
I’ll undo that, now.
Let’s remove this formula and look at another way to enter it using the point-and-click method.
Start the formula normally.
Then nudge the first sheet, Week1.
Now hold the Shift key down and nudge the last sheet, Week5.
Excel builds the 3D reference.
To complete the formula, click D6 and press Enter.
Notice that Excel doesn’t care about the names of the sheets when you build a 3D reference.
It simply includes all sheets between the first and the last sheet.
If we rename Week1, everything still works fine, but the reference is updated in the formula.
If I move Week1 back to its original location, everything works the same as before.
