Andrew and David have similar Excel issues - they need a Summary worksheet to return values from all other worksheets and want a simple way to enter one formula that can be copied to point to the other worksheets. Today, in episode 1822, how to use INDIRECT to solve the problem when the Total row is on the same row in every worksheet.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Podcast Episode 1822: Summarize Worksheets With INDIRECT.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, today, I have two very similar questions sent in by two different people, so I'm going to attack this over two episodes.
Andrew Z, “I’m trying to make a table by pulling data from other sheets.
Is there a way to do this quickly?
I’ve tried using the sheet name and reference…even an absolute reference…but I’m unable to change the sheet name as I copy this formula down, so we're going to do that in episode 1822.
Tomorrow, in 1823, David from Fort Myers had the same problem but his total row is on a different row on each worksheet, so we'll do that tomorrow -- a similar question but a little bit different.
So, here we have worksheets for AT&T.
The total is always in row 1. CitiGroup, Exxon, Ford, General Electric, General Motors, Wal-Mart -- and we want to build a summary back here.
So, the first thing we're going to do is we're just going to build the first formula.
Type an = sign, click on the AT&T tab, point to cell E1, I’ll press F4 one, two, two times to lock down the row but not the column, and then press CTRL+ENTER, and the formula that we get here is an = sign, ‘, the sheet name, another ‘, !, and then COLUMN, $, 1.
[ =‘AT&T’!E$1 ] Parts of this need to change as we copy the formula through, and the particularly tough part is the customer name and it points to a different worksheet.
So, we're going to use a function, an obscure function, called =INDIRECT, and what INDIRECT does, it says I want you to analyze what's inside the INDIRECT, and when you're done analyzing that, it's going to look exactly like a cell reference, and once you get that, go out to that cell reference and return the value, alright?
So, we need to build a cell reference that looks exactly like this and so the first thing is that apostrophe.
I’m going to put the apostrophe in quotes, so there’s “’” and then an &. After the &, we’re going to point to cell D2, I’m going to press F4 one, two, three times to make sure that we always point back to D, and then another & and delete.
I'll put a “ and then the closing ‘ and the !.
Alright.
So we’ve now gotten up to the point where we need to point to column E. Boy, and there are probably a lot of different ways to do this but the way that it comes to my mind right here right now at 5 in the morning is &, the MID of ABCDEFGH, comma, the current column -- in this case, that's five – comma, 1 will return us the column letter that we're currently in.
We might be able to do this using the address but this is the way I want to go, and then “, the $ sign, 1, ”, and ). [ =INDIRECT(“’”&$D2&“’!”&MID(“ABCDEFGH”,COLUMN(),1)&“$1”) ] Alright.
So, we have that formula and it's returning the exact same value.
Let's just take a look and see what this formula is doing if we take that whole bit there and press F9.
It is -- that's funny, it's returning an array -- returning something that looks exactly like that, and as we copy it throughout and I go, for example, over here and press F9, you'll see that it's now pointing to Ford in “”, !, G, $ SIGN, 1.
[ =INDIRECT({“’FORD’!G$1”}) ] So, we're being clever.
We're using formulas to build a formula, in essence, and it is returning the correct value throughout.
So, for Andrew, this INDIRECT, because he's always pointing to the same row number every time -- in my case, it's row 1; in his case, it's probably something different -- this simple, well, I don’t know if it’s simple, but this INDIRECT will solve the problem.
Now, tomorrow, episode 1823, David, his totals are in a different spot every time, so we'll take a look at how to solve that.
Well, hey, I want to thank Andrew and David for sending in those questions and I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Podcast Episode 1822: Summarize Worksheets With INDIRECT.
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, today, I have two very similar questions sent in by two different people, so I'm going to attack this over two episodes.
Andrew Z, “I’m trying to make a table by pulling data from other sheets.
Is there a way to do this quickly?
I’ve tried using the sheet name and reference…even an absolute reference…but I’m unable to change the sheet name as I copy this formula down, so we're going to do that in episode 1822.
Tomorrow, in 1823, David from Fort Myers had the same problem but his total row is on a different row on each worksheet, so we'll do that tomorrow -- a similar question but a little bit different.
So, here we have worksheets for AT&T.
The total is always in row 1. CitiGroup, Exxon, Ford, General Electric, General Motors, Wal-Mart -- and we want to build a summary back here.
So, the first thing we're going to do is we're just going to build the first formula.
Type an = sign, click on the AT&T tab, point to cell E1, I’ll press F4 one, two, two times to lock down the row but not the column, and then press CTRL+ENTER, and the formula that we get here is an = sign, ‘, the sheet name, another ‘, !, and then COLUMN, $, 1.
[ =‘AT&T’!E$1 ] Parts of this need to change as we copy the formula through, and the particularly tough part is the customer name and it points to a different worksheet.
So, we're going to use a function, an obscure function, called =INDIRECT, and what INDIRECT does, it says I want you to analyze what's inside the INDIRECT, and when you're done analyzing that, it's going to look exactly like a cell reference, and once you get that, go out to that cell reference and return the value, alright?
So, we need to build a cell reference that looks exactly like this and so the first thing is that apostrophe.
I’m going to put the apostrophe in quotes, so there’s “’” and then an &. After the &, we’re going to point to cell D2, I’m going to press F4 one, two, three times to make sure that we always point back to D, and then another & and delete.
I'll put a “ and then the closing ‘ and the !.
Alright.
So we’ve now gotten up to the point where we need to point to column E. Boy, and there are probably a lot of different ways to do this but the way that it comes to my mind right here right now at 5 in the morning is &, the MID of ABCDEFGH, comma, the current column -- in this case, that's five – comma, 1 will return us the column letter that we're currently in.
We might be able to do this using the address but this is the way I want to go, and then “, the $ sign, 1, ”, and ). [ =INDIRECT(“’”&$D2&“’!”&MID(“ABCDEFGH”,COLUMN(),1)&“$1”) ] Alright.
So, we have that formula and it's returning the exact same value.
Let's just take a look and see what this formula is doing if we take that whole bit there and press F9.
It is -- that's funny, it's returning an array -- returning something that looks exactly like that, and as we copy it throughout and I go, for example, over here and press F9, you'll see that it's now pointing to Ford in “”, !, G, $ SIGN, 1.
[ =INDIRECT({“’FORD’!G$1”}) ] So, we're being clever.
We're using formulas to build a formula, in essence, and it is returning the correct value throughout.
So, for Andrew, this INDIRECT, because he's always pointing to the same row number every time -- in my case, it's row 1; in his case, it's probably something different -- this simple, well, I don’t know if it’s simple, but this INDIRECT will solve the problem.
Now, tomorrow, episode 1823, David, his totals are in a different spot every time, so we'll take a look at how to solve that.
Well, hey, I want to thank Andrew and David for sending in those questions and I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
