Join Dennis Taylor for an in-depth discussion in this video Create dynamic sheet name references with the INDIRECT function, part of Excel Tips Weekly.
- [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.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Create dynamic sheet name references with the INDIRECT function