In this video, we'll look at how to create a dynamic reference to a worksheet in a formula.
Let's take a look.
Sometimes you want to reference a worksheet dynamically in a formula, so that it can be easily changed.
In this workbook, we have 5 weeks of test scores. 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 in the formulas we'll be using and hardcode a reference a single worksheet. That way, we can see the syntax we'll need.
First, I'll enter the MAX function. When I enter the range, you can see that Excel automatically adds the sheet name plus an exclamation mark, then the range.
Next, I'll copy that formula twice (using the shortcut Control + apostrophe to keep the reference from changing) and come back and change the function names.
One thing to notice: if the a worksheet contains any space characters, you'll need to enclose the sheet name in single quotes. If I change the name of Week1 to include a space, you'll see can 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 the single quotes. That way, it will always work.
OK, 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. They key is to build a text string that represents a full reference, and use INDIRECT to transform the 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 concatenate the reference we need.
I'll copy this syntax and use it to update first formula. For the sheet, we're going to pick up the value from C5, so I'll make C5 an absolute reference. For the reference, we the range D6:D38. We need to enclose this in double quotes since we're building a text string.
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 pull information from that sheet.
Finally, let's make C5 a dropdown menu, so it's easier to select a worksheet.
We get a #REF error with Week1 because there's still a space in the sheet name. When I take that out, everything works.
The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.