Rod asks how to set up a summary worksheet that will selectively point to different worksheets. The solution is the INDIRECT function in combination with some other functions. Episode 602 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a question was asked by Rod.
Rod's from Cincinnati.
He was at the power analyst boot camp in Chicago, last week.
Thought it was a pretty interesting question and it actually took us a little bit of time to get it working properly.
So, I want to go through, what's up there.
He basically had a series of different workbooks for different departments.
In my example, I just have Apple, Banana, Cherry, Eggplant, Grapes and Kale and on the summary page.
He wanted to be able to selectively grab numbers from a certain worksheet, based on a value that the person chose.
So, he set up a little data validation here with a list and they could choose one of the departments and when they chose that department he wanted the formulas to go grab values from that certain worksheet.
Well, the solution to this is something called indirect.
The indirect function and basically an indirect instead of putting a specific cell function.
You're going to put a calculation or a text value that refers to a specific cell.
So, for example if I wanted to grab F2.
I might put a little concatenation in here and say that I wanted in quotes F ampersand (&) and then instead of using A2.
I'm going to put the row of A2.
Row of A2 of course will give me two and w'ell close that.
Sure enough, it grabs the 46 from the cell but the nice thing is I copy this down.
The row of A2 changes.
I mean, so it's pointing to a different value.
We say ok! That seems kind of pointless but it helps in this particular situation.
If I need to build a link back to another worksheet.
Let's just build a hard-Coded one.
I'm going to type the equal sign, click on Apple, click on B2 and I'll see that the proper syntax for that formula up here, in the formula bar is equal Apple! B2.
Now, sometimes you might have a worksheet tab that has a space in the middle.
So, let me just put here Apple [ space ] sales and we'll go back and look at our original formula.
You see that as soon as the worksheet name has a space in the form of the bar.
They wrap the sheet name and apostrophes, okay!
So, let's take a look at the formula that build for Rod.
If I edit that formula, basically you have equal indirect and then what I have is quote, apostrophe, quote that gets that leading apostrophe end and (&) ampersand and then C16 is the value where the user chooses banana, cherry.
They basically choose the department after that put the ampersand (&) and then an apostrophe to close the sheet name and an exclamation point (!).
Now, in this case all the department names were single words.
They didn't have any spaces but I went ahead and put the apostrophes in just in case some day in the future someone adds a new sheet with spaces, that way to work.
And then once I have the sheet name called out, I basically need to find a way to specify B2.
I'm using a different function here called the address function.
The address function, I have to give it a row and a column and Excel will go through and build that particular address.
So, I asked for the row B2 and the column of B2.
The nice thing is as we copy that, of course it will work.
Let's just do a quick little test here.
Let's see what happens when we evaluate this formula choose the form of the tools, formula auditing, evaluate formula, and basically you'll see that the C16 is evaluated, in this case to banana and it goes ahead and build the apostrophe on each side and the exclamation point (!).
Now, the row of B2 is the number 2.
The column of B2 is the number 2 and this is interesting the address of 2, comma, 2 is the text dollar sign, B, dollar sign, 2.
So, now Excel basically has a situation where the formula is pointing to banana !, dollar sign, B, dollar sign 2.
The indirect of course goes and grabs that value from the banana sheet and gives us the 75.
When we choose a different sheet name that whole calculation happens again.
But this time it evaluates to a reference to the Cherry worksheet.
Very cool using indirect, you can create a nice little summary report here.
Someone chooses the department and it goes and grabs the values from that specific department.
Indirect very cool, the big problem I had when I try to build indirect.
I look at my original formulas at all it starts with an equal sign and I put the equal sign in.
You don't put the equal sign in the middle of indirect, you're just looking for the whole cell reference that follows the equal sign.
So, great question from Rod.
Thanks for sending it in and hopefully the next time that you need to do this.
You can think about the indirect function.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, we have a question was asked by Rod.
Rod's from Cincinnati.
He was at the power analyst boot camp in Chicago, last week.
Thought it was a pretty interesting question and it actually took us a little bit of time to get it working properly.
So, I want to go through, what's up there.
He basically had a series of different workbooks for different departments.
In my example, I just have Apple, Banana, Cherry, Eggplant, Grapes and Kale and on the summary page.
He wanted to be able to selectively grab numbers from a certain worksheet, based on a value that the person chose.
So, he set up a little data validation here with a list and they could choose one of the departments and when they chose that department he wanted the formulas to go grab values from that certain worksheet.
Well, the solution to this is something called indirect.
The indirect function and basically an indirect instead of putting a specific cell function.
You're going to put a calculation or a text value that refers to a specific cell.
So, for example if I wanted to grab F2.
I might put a little concatenation in here and say that I wanted in quotes F ampersand (&) and then instead of using A2.
I'm going to put the row of A2.
Row of A2 of course will give me two and w'ell close that.
Sure enough, it grabs the 46 from the cell but the nice thing is I copy this down.
The row of A2 changes.
I mean, so it's pointing to a different value.
We say ok! That seems kind of pointless but it helps in this particular situation.
If I need to build a link back to another worksheet.
Let's just build a hard-Coded one.
I'm going to type the equal sign, click on Apple, click on B2 and I'll see that the proper syntax for that formula up here, in the formula bar is equal Apple! B2.
Now, sometimes you might have a worksheet tab that has a space in the middle.
So, let me just put here Apple [ space ] sales and we'll go back and look at our original formula.
You see that as soon as the worksheet name has a space in the form of the bar.
They wrap the sheet name and apostrophes, okay!
So, let's take a look at the formula that build for Rod.
If I edit that formula, basically you have equal indirect and then what I have is quote, apostrophe, quote that gets that leading apostrophe end and (&) ampersand and then C16 is the value where the user chooses banana, cherry.
They basically choose the department after that put the ampersand (&) and then an apostrophe to close the sheet name and an exclamation point (!).
Now, in this case all the department names were single words.
They didn't have any spaces but I went ahead and put the apostrophes in just in case some day in the future someone adds a new sheet with spaces, that way to work.
And then once I have the sheet name called out, I basically need to find a way to specify B2.
I'm using a different function here called the address function.
The address function, I have to give it a row and a column and Excel will go through and build that particular address.
So, I asked for the row B2 and the column of B2.
The nice thing is as we copy that, of course it will work.
Let's just do a quick little test here.
Let's see what happens when we evaluate this formula choose the form of the tools, formula auditing, evaluate formula, and basically you'll see that the C16 is evaluated, in this case to banana and it goes ahead and build the apostrophe on each side and the exclamation point (!).
Now, the row of B2 is the number 2.
The column of B2 is the number 2 and this is interesting the address of 2, comma, 2 is the text dollar sign, B, dollar sign, 2.
So, now Excel basically has a situation where the formula is pointing to banana !, dollar sign, B, dollar sign 2.
The indirect of course goes and grabs that value from the banana sheet and gives us the 75.
When we choose a different sheet name that whole calculation happens again.
But this time it evaluates to a reference to the Cherry worksheet.
Very cool using indirect, you can create a nice little summary report here.
Someone chooses the department and it goes and grabs the values from that specific department.
Indirect very cool, the big problem I had when I try to build indirect.
I look at my original formulas at all it starts with an equal sign and I put the equal sign in.
You don't put the equal sign in the middle of indirect, you're just looking for the whole cell reference that follows the equal sign.
So, great question from Rod.
Thanks for sending it in and hopefully the next time that you need to do this.
You can think about the indirect function.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
