Create dynamic sheet name references with the INDIRECT function

- [Voiceover] In this workbook, there are a number of different sheets you can see at the bottom of the screen for different states. Currently we're showing seven states. Eventually, we will add more. On the first sheet here called Totals, we've got Population Totals out here, but we want to get the Sales Total for each of the states. As I click California, you see the layout here. The total that we're looking for is in cell G7. Next state over, Texas. Same layout as it is for all these. So in each case, we want to get the data out of cell G7 for the different states here.

They all have exactly the same layout. If I were doing this for California for starters, I'd type equal. I'm not going to type in a sheet name, I rarely do, I'll click California, and then I'll click the cell G7 and press enter. And what does this look like? There's the sheet name followed by an exclamation point. I did not type that. Those of you who have worked with the sheet references recognize that when you click a sheet, the exclamation point automatically appears there, and then G7.

Now, I'd like to be able to copy this down the column to pick up the data for the other states. However, if I simply copy this downward, I'm going to get data from cell G8, G9 on the California sheet. For example, I just copied a few cells. What's happened down here? That's California sheet cell G9. Obviously, I don't want that. And furthermore, I'm going to be adding to this list. Eventually, we might have all 50 states in this list. So I want to make it easy to set these up. Now, I'm going to double-click here and simply copy this information, highlight it all, press Control + C and Escape.

I'm going to be pasting it over here in column D, just for reference. I'll type an initial space, and then Control + V to space. Good enough. Make the column a little bit wider so we can see it better. Here's what we'd like to simulate. Though, who would ever guess that there's a function set up for this, and if you were trying to find it, I think it would take you a good deal of time to find it. One by one we can start looking at the various categories here. I'll begin on Lookup & Reference because I know it's found here, but I think it would take you a while to find it.

Here's the function called Indirect. If this were out of context and we just happen to see this one day, returns the reference specified by a text string, that is the function we will use here. It's not commonly used and yet, it's ideally used in the example we're about to see. I don't think, if you were just fishing around one day or looking at different function names, this one would grab your attention that much. But when you use it here, in cell B2, equal, indirect, left parenthesis.

I want to pick up the data now from cell A2, I'm gonna click that. Now, looking at the formula over in D2 that I copied, I'm trying to simulate that look. So let's say that right now I've picked up the word California. What else do I need in the formula? Along with, and we use the the ampersand sign, meaning and. So imagine, the word California& what do we need after this? Within double quotes, we need an exclamation point and then G7. Doesn't have to be capitalized, but why not.

Double quote, right parenthesis. And as I press Enter, we're hopefully going to see the same number we saw before. Since I will be copying down the column, instead of pressing Enter, I'll press Control + Enter. Now it's looking good. I'll double-click to copy this down the column. It looks like we've got some believable numbers. The Texas number, 19,179. What is the formula saying as we look at this? Go get the letters T E X A S out of cell A3, combine them with the exclamation point and the G7.

If this is correct, and we assume it will be, let's go over to Texas and check it out. 19,179. There it is. Looking ahead, Florida, 14,155. Let's come back and check that out. That's working as well, too. And so, as we add additional states here, all of which will have the same layout, this formula's going to work just fine. Now, what if I change the name of NewYork to have a space in it? The NewYork sheet. I'm gonna double-click the NewYork sheet and put a space between the W and the Y.

Let's go back to our totals here, we've got a problem. What happens over here now if I double-click NewYork, put a space between the W and the Y? Doesn't work. So we've gotta be alert to that idea. Is there a workaround somehow? What do we need to do in a case like this? If we were writing this manually, and I will do it now. Equal, and I'm going to click down here, the NewYork sheet, and then go to cell G7 and press Enter. We now have an answer. What does that look like? Any time a sheet name has spaces in it, you will see single quotes in addition to the exclamation point.

So I'm going to copy this information with Control + C and Escape, and paste it over here for reference, as I did earlier with California. Control + V and Return and leave the data that way. So, let's try this idea. Let's simulate using Indirect as we did before. Left parenthesis, we want to pick up that state name, A5. Ampersand, meaning and. And now, within double quotes, what do we need here? In other words, we will have picked up New York, wouldn't we.

Actually, before New York though, we need double quote, single quote, double quote. In other words, we need a single quote and we need to pick up the phrase New York, N-E-W space Y-O-R-K. And then, following that, we need to put in a single quote, then the exclamation point , and then G7, and then a final double quote. Double quote is referred to as a string delimiter. And a right parenthesis here will complete the entry.

And we have an answer there. Now that should work for the others, even though it's not necessary to have the single quotes on the others. It's working there. I double-clicked and I'll click and drag upward. If any of the states have spaces in them, and they might. We have a lot of North Carolina, North Dakota, South Dakota, so on, all those multi-state names. We might have to revert to this idea. If all your sheet tab names are without spaces, we wouldn't necessarily need it to go this far, but it shows some of the fine-tuning capabilities that Excel gives us in situations like this.

Ultimately, this will work. As our list grows, as we add additional states, this use of the Indirect function will give us the answers that we need. An unusual function, and yet a powerful function, as we've seen in this set of examples.