Today, in Episode #1319, Mike "Excel Is Fun" Girvin and Bill "MrExcel" Jelen show us the options available to create a formula that defines the address of a cell containing a total. Using VLOOKUP, MATCH the ADDRESS Function and a few other tricks, getting that address becomes easy!
Dueling Excel Podcast #61...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
AND
"Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen.
For all of your Microsoft Excel needs visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
Dueling Excel Podcast #61...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
AND
"Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen.
For all of your Microsoft Excel needs visit MrExcel.com - Your One Stop for Excel Tips and Solutions.
Transcript of the video:
Hey all right welcome back.
It's another dueling Excel podcast. I'm Bill Jelen from MrExcel.
[ By joining my current from Excels my concern ] in this question, sometimes you just look at the stuff and say why do they want to do this?
I have a whole range of cells here.
I'm gonna hide a bunch of these rows that we can see what's going on here.
Okay so 4,5, row 179, row 180 is where the total happens to be.
They want formula here that's going to create the address of this cell with the total right now maybe they want to use that in an indirect type, I don't know but anyway if they insert more rows, they would figure out where that total is.
All right, so the first thing I did is I used one of the tricks from Excel Gurus Gone Wild and it has to do either a vlookup or a match for a number that has to be larger than any possible number and instead of doing the comma falls at the end, we're going to do comma true or well technically, it should be a 1, if we're doing a match and what that does?
Is it says hey I want to find the value that is just larger than 9999999 but if there isn't 1, it's going to give us the last numeric value in the column and in this case, will tell us that that is in row 180 and then to finish that off, we'll just say = "A" &MATCH Now initially I thought about trying to look for the max and actually instead of looking for 9999999, to look for a max of column A, something like this.
Knowing that, that would in theory give us the total but you always worry about some weird situation, where they had a thousand dollar sale and a one dollar credit and the totals only 999 and so, I go back to that original formula.
All right Mike! let's see what you have?
Thanks to MrExcel.
Wow! what I love about these duels Excel is a 180° directionally away from the approach I'm gonna take.
God I love it, E and then match.
All right, um I'm gonna use the Address function.
Address will give you the address of a cell if you get a row number, a column number.
So I'm gonna say hey row, ROW of what?
Now, the goal here is to Insert and have the address of this total down here, right.
So if I click on this cell and you can see up here row A180, then, if I insert any this will be pushed down and that reference will update.
So I'm going to close parentheses and then comma scroll all the way up.
Same thing I'm gonna do column.
Oh! I could spell column and I'll do A180.
All right, so the first two arguments row and column, you just tell it and it will give us the Address.
Now comma, the next part is Absolute, Mixed or Relative.
So I'm gonna put a 4 and the final argument is which style, so and by default it's A1, so we're going to leave that out, backspace.
Let me... so we've got a 4 there and then boom!
That'll do it. A180.
Another way you could do this, is maybe, even a little bit easier, if you don't mind absolute.
Hey! just use the CELL function Address, in, we're in 2010 so you get this drop-down.
there's also some amazing things that cell function can do.
Boom and then comma, the reference.
a180. Right and so then, there's dollar signs.
Now let's go ahead and test this, right click the... the row, headers.
Right click Insert and sure enough an update, you know that's kind of annoying there, if you like this approach, maybe, we could do something like this.
Right you know like those dollar signs, so how about SUBSTITUTE.
Now the text that's the text for substitute, comma the old texts we don't like, it in double quotes, $ sign and then that's the old text.
comma and the new text, hey, let's just do double quote, double quote for blank.
Close parentheses. All right, I'll throw it back to MrExcel.
Oh hey Mike, that's really cool, you're counting on the fact that if, they insert new rows, that total is going to move and because you refer to the cell it's going to keep pointing that cell.
You are right, I thought that maybe they were just end and our total randomly in some other cell or something like that.
So if, you're going with that approach which I love, then why don't we do =A and row of A180, which will give us A, yeah, A180 and if we would later Insert some rows, those move down and it changes like that.
Cool! I love these dueling podcasts.
Hey, thanks everyone for stopping by.
See you next time for another dueling Excel podcast from MrExcel and Excel is Fun.
It's another dueling Excel podcast. I'm Bill Jelen from MrExcel.
[ By joining my current from Excels my concern ] in this question, sometimes you just look at the stuff and say why do they want to do this?
I have a whole range of cells here.
I'm gonna hide a bunch of these rows that we can see what's going on here.
Okay so 4,5, row 179, row 180 is where the total happens to be.
They want formula here that's going to create the address of this cell with the total right now maybe they want to use that in an indirect type, I don't know but anyway if they insert more rows, they would figure out where that total is.
All right, so the first thing I did is I used one of the tricks from Excel Gurus Gone Wild and it has to do either a vlookup or a match for a number that has to be larger than any possible number and instead of doing the comma falls at the end, we're going to do comma true or well technically, it should be a 1, if we're doing a match and what that does?
Is it says hey I want to find the value that is just larger than 9999999 but if there isn't 1, it's going to give us the last numeric value in the column and in this case, will tell us that that is in row 180 and then to finish that off, we'll just say = "A" &MATCH Now initially I thought about trying to look for the max and actually instead of looking for 9999999, to look for a max of column A, something like this.
Knowing that, that would in theory give us the total but you always worry about some weird situation, where they had a thousand dollar sale and a one dollar credit and the totals only 999 and so, I go back to that original formula.
All right Mike! let's see what you have?
Thanks to MrExcel.
Wow! what I love about these duels Excel is a 180° directionally away from the approach I'm gonna take.
God I love it, E and then match.
All right, um I'm gonna use the Address function.
Address will give you the address of a cell if you get a row number, a column number.
So I'm gonna say hey row, ROW of what?
Now, the goal here is to Insert and have the address of this total down here, right.
So if I click on this cell and you can see up here row A180, then, if I insert any this will be pushed down and that reference will update.
So I'm going to close parentheses and then comma scroll all the way up.
Same thing I'm gonna do column.
Oh! I could spell column and I'll do A180.
All right, so the first two arguments row and column, you just tell it and it will give us the Address.
Now comma, the next part is Absolute, Mixed or Relative.
So I'm gonna put a 4 and the final argument is which style, so and by default it's A1, so we're going to leave that out, backspace.
Let me... so we've got a 4 there and then boom!
That'll do it. A180.
Another way you could do this, is maybe, even a little bit easier, if you don't mind absolute.
Hey! just use the CELL function Address, in, we're in 2010 so you get this drop-down.
there's also some amazing things that cell function can do.
Boom and then comma, the reference.
a180. Right and so then, there's dollar signs.
Now let's go ahead and test this, right click the... the row, headers.
Right click Insert and sure enough an update, you know that's kind of annoying there, if you like this approach, maybe, we could do something like this.
Right you know like those dollar signs, so how about SUBSTITUTE.
Now the text that's the text for substitute, comma the old texts we don't like, it in double quotes, $ sign and then that's the old text.
comma and the new text, hey, let's just do double quote, double quote for blank.
Close parentheses. All right, I'll throw it back to MrExcel.
Oh hey Mike, that's really cool, you're counting on the fact that if, they insert new rows, that total is going to move and because you refer to the cell it's going to keep pointing that cell.
You are right, I thought that maybe they were just end and our total randomly in some other cell or something like that.
So if, you're going with that approach which I love, then why don't we do =A and row of A180, which will give us A, yeah, A180 and if we would later Insert some rows, those move down and it changes like that.
Cool! I love these dueling podcasts.
Hey, thanks everyone for stopping by.
See you next time for another dueling Excel podcast from MrExcel and Excel is Fun.
