Create a dynamic reference to a worksheet

In this video we'll look at how to create a dynamic reference to a worksheet in a formula.

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. Let's assume we want to build a simple dashboard that shows the Maximum score, the Minimum score, and the Average score for whichever worksheet a user selects.

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.

 I'll start with the MAX function. When I enter the range and point to a different worksheet, you can see that Excel automatically adds the sheet name, plus an exclamation mark, and then the cell range. That's the syntax we need to use.

Next, I'll copy the formula twice (using the shortcut Control + apostrophe to keep the reference from changing), then I'll come back and change the function names.

One thing to notice: if a worksheet contains any space characters in the name, you need to enclose the sheet name in single quotes. If I change the name of Week1 to include a space, you'll see that Excel automatically adds these single quotes to the reference for you.

Because you never know when a sheet name will contain a space, it makes sense to build a reference that automatically includes these single quotes. That way, it will always work.

So now let's make these formulas dynamic so that we can change the week in C5 and get results from a different worksheet.

To do this, we'll use the INDIRECT function. The key is to build a text string that represents a full reference, then use INDIRECT to transform that text into an actual reference.

The formula we'll need for this is in cell F9. We need to concatenate the sheet name with a single quote at the start and a single quote + an exclamation mark at the end. 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. For the sheet, we're going to pick up the value from C5, so I'll make C5 an absolute reference. 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.

Now when I type a different sheet name in C5, the formulas automatically pull information from that sheet.

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.