David from Fort Myers has one worksheet for each expense category. The total row appears on a different row in each worksheet. On a Summary worksheet, he would like to grab the totals from each worksheet. In today's episode, how to use INDIRECT as the 2nd argument in VLOOKUP to solve the problem.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Podcast Episode 1823: Summary Table Grabbing A Total Row From Each Worksheet.
Hey. Welcome back to the MrExcel netcast.
I’m Bill Jelen. Today's question from David in my Fort Meyers seminar -- David had a series of twelve different worksheets. Each worksheet was for one expense and, at the bottom of each worksheet, there was a TOTAL row. He was trying to build on the summary page a summary table that would pull the TOTAL row from each of the different sheets, and see, the big problem, so, yesterday, in episode 1822, Andrew was trying to grab always row 1 from every single sheet or row 10 or row 15. It was a hard-coded row.
Now, we have the TOTALs in a different spot.
So, here, for AT&T, it's in row 42. For Citigroup, it’s in row 50. For Exxon, it's in row 68.
So, we need to go find the TOTAL.
Well, just like I did yesterday, I'm going to build a regular formula first that cannot be copied down. So, we're going to look up the word TOTAL back here on the AT&T sheet and we'll choose columns A through H. Now, I want to lock this down to the columns and, when I press F4, it puts the $ signs before the A and the H -- I don't have to press it three times -- and then which column do we want? What happens if we want the 5th column?
1, 2, 3, 4, 5, and this formula is being entered in the 5th column so I can simply say COLUMN.
Now, I lucked out there that my summary table lines up with the columns on the individual sheets. You might need to adjust this by adding +1, -1 to get the column numbers to line up, and then of course FALSE. Every VLOOKUP has to end at FALSE, and we're just going do a test here, make sure that that is copying across correctly, getting the right answers from the AT&T page, and so you know that is working great, but I need to copy this down and unfortunately this is hard-coded to always go grab the value from AT&T.
So, just as yesterday, when I used INDIRECT, we're going to use INDIRECT again but this time the INDIRECT is only going to be used for the second argument of the VLOOKUP. So, here, INDIRECT. Inside the INDIRECT, we have to build by concatenating enough stuff to get a reference that looks like this reference over here. So, we need that opening ', so I'll put a “, and then right arrow to get on the other side of the ’, another ”, and then &. Alright, so we're joining the ‘ together with whatever is in D2. I’ll put a $ sign before the D, and then another &, and I'll delete the AT&T here, “, include the ‘, include the !, include the A through H, and then close the ”, close the ) for the INDIRECT. [ =VLOOKUP“TOTAL”,INDIRECT(“’”&$D2&“’!$A:$H”),COLUMN(),FALSE) ] Alright. So, this is pretty cool. Here, it's going to go take the sheet name from column D, it's going to append it with A through H, pass that to INDIRECT, which will then tell VLOOPKUP that that's where our lookup table lives, and so I'll press CONTROL+ENTER here and we'll get the same answers but, as I copy this down, then we are getting different answers from each of the other worksheets.
Alright, so, this is a cool use of INDIRECT.
Yesterday, in 1822, we used INDIRECT to always go get the TOTALs from row 1. Here, we're using VLOOPKUP to go get the TOTAL rows from wherever they are. Now, tomorrow, episode 1824, I have another example of INDIRECT but this is where INDIRECT has gone completely wrong with hundreds or thousands of INDIRECTs and it is slowing the spreadsheet down, and then, in that case, we’ll have a different way to work around.
Hey, also, I noticed in the comments yesterday for 1822 -- let me switch back here to the 1822 -- in order to point to column 5, 6, 7, and 8, I had used the MID function with the COLUMN and Xlarium proposed the shorter R1C”&COLUMN, column returns 5 in column E.
Now, this is interesting. I'd never thought about using R1C1.
notation in INDIRECT but if you add the optional second parameter of ,FALSE, that works, so kudos to Xlarium for that. Great idea -- a cool way to improve 1822.
Back here at 1823. So, we got a nice triumvirate of podcast episodes -- 1822 yesterday, 1823 today, and then 1824 tomorrow -- to show you a way to do all this without INDIRECT.
Alright. Well, hey, I want to thank Andrew for yesterday's question, David for today's question, and I want to thank you for stopping by. We'll see you next time for another netcast with MrExcel.
Learn Excel from MrExcel, Podcast Episode 1823: Summary Table Grabbing A Total Row From Each Worksheet.
Hey. Welcome back to the MrExcel netcast.
I’m Bill Jelen. Today's question from David in my Fort Meyers seminar -- David had a series of twelve different worksheets. Each worksheet was for one expense and, at the bottom of each worksheet, there was a TOTAL row. He was trying to build on the summary page a summary table that would pull the TOTAL row from each of the different sheets, and see, the big problem, so, yesterday, in episode 1822, Andrew was trying to grab always row 1 from every single sheet or row 10 or row 15. It was a hard-coded row.
Now, we have the TOTALs in a different spot.
So, here, for AT&T, it's in row 42. For Citigroup, it’s in row 50. For Exxon, it's in row 68.
So, we need to go find the TOTAL.
Well, just like I did yesterday, I'm going to build a regular formula first that cannot be copied down. So, we're going to look up the word TOTAL back here on the AT&T sheet and we'll choose columns A through H. Now, I want to lock this down to the columns and, when I press F4, it puts the $ signs before the A and the H -- I don't have to press it three times -- and then which column do we want? What happens if we want the 5th column?
1, 2, 3, 4, 5, and this formula is being entered in the 5th column so I can simply say COLUMN.
Now, I lucked out there that my summary table lines up with the columns on the individual sheets. You might need to adjust this by adding +1, -1 to get the column numbers to line up, and then of course FALSE. Every VLOOKUP has to end at FALSE, and we're just going do a test here, make sure that that is copying across correctly, getting the right answers from the AT&T page, and so you know that is working great, but I need to copy this down and unfortunately this is hard-coded to always go grab the value from AT&T.
So, just as yesterday, when I used INDIRECT, we're going to use INDIRECT again but this time the INDIRECT is only going to be used for the second argument of the VLOOKUP. So, here, INDIRECT. Inside the INDIRECT, we have to build by concatenating enough stuff to get a reference that looks like this reference over here. So, we need that opening ', so I'll put a “, and then right arrow to get on the other side of the ’, another ”, and then &. Alright, so we're joining the ‘ together with whatever is in D2. I’ll put a $ sign before the D, and then another &, and I'll delete the AT&T here, “, include the ‘, include the !, include the A through H, and then close the ”, close the ) for the INDIRECT. [ =VLOOKUP“TOTAL”,INDIRECT(“’”&$D2&“’!$A:$H”),COLUMN(),FALSE) ] Alright. So, this is pretty cool. Here, it's going to go take the sheet name from column D, it's going to append it with A through H, pass that to INDIRECT, which will then tell VLOOPKUP that that's where our lookup table lives, and so I'll press CONTROL+ENTER here and we'll get the same answers but, as I copy this down, then we are getting different answers from each of the other worksheets.
Alright, so, this is a cool use of INDIRECT.
Yesterday, in 1822, we used INDIRECT to always go get the TOTALs from row 1. Here, we're using VLOOPKUP to go get the TOTAL rows from wherever they are. Now, tomorrow, episode 1824, I have another example of INDIRECT but this is where INDIRECT has gone completely wrong with hundreds or thousands of INDIRECTs and it is slowing the spreadsheet down, and then, in that case, we’ll have a different way to work around.
Hey, also, I noticed in the comments yesterday for 1822 -- let me switch back here to the 1822 -- in order to point to column 5, 6, 7, and 8, I had used the MID function with the COLUMN and Xlarium proposed the shorter R1C”&COLUMN, column returns 5 in column E.
Now, this is interesting. I'd never thought about using R1C1.
notation in INDIRECT but if you add the optional second parameter of ,FALSE, that works, so kudos to Xlarium for that. Great idea -- a cool way to improve 1822.
Back here at 1823. So, we got a nice triumvirate of podcast episodes -- 1822 yesterday, 1823 today, and then 1824 tomorrow -- to show you a way to do all this without INDIRECT.
Alright. Well, hey, I want to thank Andrew for yesterday's question, David for today's question, and I want to thank you for stopping by. We'll see you next time for another netcast with MrExcel.
