You have a workbook with one sheet for each day in the month. A summary worksheet in the workbook needs to grab data from each worksheet based on the date in column A. Episode 819 will show you how to use INDIRECT to solve this problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Alright. Welcome back to the MrExcel netcast. I'm Bill Jelen.
I have an interesting spreadsheet setup today in that I have 31 worksheets in the workbook, one for each day, and their name for each day -- so, for example, here is AUGUST 31st 2008 -- and, then, back on my SUMMARY tab, I need to grab the SALES from each day.
Now, the SALES are in cell B3 but I want a way to be able to take that date in column A and pull the data from that particular sheet, and so I'm just going to start with some simple little formulas over here.
I'm going to use the TEXT function.
I want the TEXT of that date, and what format do I want?
In “s, I'm going to say MMM SPACE D SPACE YYYY, and what that's going to do for me is it’s going to take the date and it's going to format it so it looks like the sheet name, and then I'm going to concatenate at the end.
So, I use an & in “s, the !, and B3 with a ”, and now I get something that looks like the cell address, although, since there's spaces in the worksheet name, I need to put ‘s before and after.
Now, I can do that right here in the text function.
I can say I want an ‘ before the month and after the year.
[ =TEXT(A3,“‘mmm d yyyy’”)&“!B3” ] Alright.
So, now, I have something that looks like a cell reference.
Wouldn't it be cool if there was a way to tell Excel, hey, we want you to go to the cell referenced by cell C3, and there is.
It's called the INDIRECT function.
=INDIRECT.
I want you to go grab whatever value I calculated over there in C3, and so, on the 1st of August, we had 1556.
If I copy this down, you'll see that it changes automatically as the date changes.
We get data from different worksheets.
Isn't that cool?
[ =INDIRECT(C3) ] Now, you don't want that extra little reference sitting out here, so, of course, I'm going to go up, press F2 to edit this, copy everything but the = sign, CONTROL+C, and then, over in my INDIRECT, I'll use that as the argument in INDIRECT, create a nice little formula, copy that down, and, now, I can get rid of the extra data, and this is live.
If I would change the dates -- let's just change this to 8/8/2008 -- you'll see that the numbers change automatically.
A great function with some serious limitations.
[ =INDIRECT(TEXT(A3,“‘mmm d yyyy’”)&“!B3”) ] Now, in tomorrow's netcast, we'll take a look at what happens when we want to use INDIRECT to grab data from an external workbook -- much harder to do.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I have an interesting spreadsheet setup today in that I have 31 worksheets in the workbook, one for each day, and their name for each day -- so, for example, here is AUGUST 31st 2008 -- and, then, back on my SUMMARY tab, I need to grab the SALES from each day.
Now, the SALES are in cell B3 but I want a way to be able to take that date in column A and pull the data from that particular sheet, and so I'm just going to start with some simple little formulas over here.
I'm going to use the TEXT function.
I want the TEXT of that date, and what format do I want?
In “s, I'm going to say MMM SPACE D SPACE YYYY, and what that's going to do for me is it’s going to take the date and it's going to format it so it looks like the sheet name, and then I'm going to concatenate at the end.
So, I use an & in “s, the !, and B3 with a ”, and now I get something that looks like the cell address, although, since there's spaces in the worksheet name, I need to put ‘s before and after.
Now, I can do that right here in the text function.
I can say I want an ‘ before the month and after the year.
[ =TEXT(A3,“‘mmm d yyyy’”)&“!B3” ] Alright.
So, now, I have something that looks like a cell reference.
Wouldn't it be cool if there was a way to tell Excel, hey, we want you to go to the cell referenced by cell C3, and there is.
It's called the INDIRECT function.
=INDIRECT.
I want you to go grab whatever value I calculated over there in C3, and so, on the 1st of August, we had 1556.
If I copy this down, you'll see that it changes automatically as the date changes.
We get data from different worksheets.
Isn't that cool?
[ =INDIRECT(C3) ] Now, you don't want that extra little reference sitting out here, so, of course, I'm going to go up, press F2 to edit this, copy everything but the = sign, CONTROL+C, and then, over in my INDIRECT, I'll use that as the argument in INDIRECT, create a nice little formula, copy that down, and, now, I can get rid of the extra data, and this is live.
If I would change the dates -- let's just change this to 8/8/2008 -- you'll see that the numbers change automatically.
A great function with some serious limitations.
[ =INDIRECT(TEXT(A3,“‘mmm d yyyy’”)&“!B3”) ] Now, in tomorrow's netcast, we'll take a look at what happens when we want to use INDIRECT to grab data from an external workbook -- much harder to do.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
