Sometimes you want to reference a worksheet dynamically in a formula, so it can be easily changed.
In this workbook we have five weeks of test scores, each in the same format.
We’ll take this one step at a time.
First, let’s just add the formulas we need and hardcode a reference to a single worksheet.
That way, we can see the syntax we’ll need.
That’s the syntax we need to use.
That way, it will always work.
The formula we’ll need for this is in cell F9.
Finally, we add the cell reference we need using the ampersand to concatenate.
I’ll copy this syntax and use it to update the first formula.
We need to enclose this in double quotes since we’re building a text string and concatenating.
For the cell reference, we need the range D6:D38.
Next, I need to adapt the other two formulas to use INDIRECT in the same way.
To save time, I’ll just copy the MAX formula down and change the function names.
Finally, let’s make C5 a dropdown menu, so it’s easier to change worksheets.
We get a #REF error with Week 1 because there’s still a space in the sheet name.
When I take that out, everything works fine.
