You've probably seen the AC/DC video running in an Excel spreadsheet. The author did some cool tricks to deliver the WAV file to your hard drive. In Episode 891, I'll take a look at the code to extract an object from a workbook.
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, welcome back to the MrExcel netcast, I'm Bill Jelen.
Second part yesterday, we talked about how to do a lookup at looking at both State and County.
I used a concatenated key yesterday; I'm going to try a different method today.
First of all, I have to answer a couple of questions down here.
I know that everything is sorted by State and then, within that, by County.
First question is where does the State start?
So they chose Ohio from the drop-down, were do the Ohio records start?
We'll use =MATCH-- =MATCH.
Go find Ohio within this column over here A2 down to however much data we have.
And we have to put either ,0 or ,False at the end-- just like you would put ,false at the VLOOKUP.
And it tells me that Ohio starts in Row 322, and then I need to know how many rows does Ohio have today.
So =COUNTIF, go find our... go look through Column A and see how many of them match OH up here, and H2.
And so Ohio has 88 counties.
So that kind of tells me where I start looking for the counties in Ohio and how tall it is.
So now I can do a VLOOKUP.
=VLOOKUP, go find this County name.
But I'm not going to hard code the table array; I'm going to use an amazing function called OFFSET.
OFFSET says that our Lookup table starts from B1, and we're going to go down a certain number of rows.
Well, how many rows?
It's the number of rows where Ohio starts, and then to the right.
Well, I don't want to go any number of columns, so that becomes 0.
How many rows tall?
Well, that's the number of Counties for Ohio.
And then how many columns wide?
Well, we have Column B and Column C, so that's two columns qide.
And basically, now, that OFFSET will change every time we choose a new State.
We're back in the VLOOKUP, now we want the second column and then ,False at the end.
It's just like you would normally do with a VLOOKUP.
So Butlerrecorder.oh.gov.
Now, if I would choose a different State-- I know that Alabama also has a Butler county-- so I'll choose Alabama, and you can see that we now get the answer from Alabama, which is different, and the values here changed.
It now starts one row below B1 and there's 67 counties in Alabama.
So every time that I choose a new State, those two numbers down there are going to change.
Let's try Texas.
So it starts in Row 68-- 254 Counties in Texas.
When we choose a valid County from Texas, then the OFFSET will work, the VLOOKUP will work.
Now, if you want to put all this back together-- I show this trick many many times-- I'm down here in H9, I'm going to choose all those cells from the formula bar, except for the equal sign.
Hit Ctrl+C, and then back up here my formula will go to where it has H9 and I'll actually paste that formula there; and then come down here to H10, copy everything except for the form of the bar, Ctrl+C, back up here, H10, Ctrl+V, and now we have one huge monster formula.
And when anyone comes and looks of this they'll say, "Wow, that, you know, that's clearly the most bizarre thing I've ever seen." The real secret is, I always build those monster formulas in several pieces and then paste them all back together at the end.
Okay, so this allows us to look up two different values-- both the State and the County-- we talked about yesterday how to use a concatenated key-- which, frankly, I think it's far simpler-- but if we simply cannot add that extra column in, we don't have control over the data, you can use this OFFSET function as the second argument in the VLOOKUP and everything works alright.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Second part yesterday, we talked about how to do a lookup at looking at both State and County.
I used a concatenated key yesterday; I'm going to try a different method today.
First of all, I have to answer a couple of questions down here.
I know that everything is sorted by State and then, within that, by County.
First question is where does the State start?
So they chose Ohio from the drop-down, were do the Ohio records start?
We'll use =MATCH-- =MATCH.
Go find Ohio within this column over here A2 down to however much data we have.
And we have to put either ,0 or ,False at the end-- just like you would put ,false at the VLOOKUP.
And it tells me that Ohio starts in Row 322, and then I need to know how many rows does Ohio have today.
So =COUNTIF, go find our... go look through Column A and see how many of them match OH up here, and H2.
And so Ohio has 88 counties.
So that kind of tells me where I start looking for the counties in Ohio and how tall it is.
So now I can do a VLOOKUP.
=VLOOKUP, go find this County name.
But I'm not going to hard code the table array; I'm going to use an amazing function called OFFSET.
OFFSET says that our Lookup table starts from B1, and we're going to go down a certain number of rows.
Well, how many rows?
It's the number of rows where Ohio starts, and then to the right.
Well, I don't want to go any number of columns, so that becomes 0.
How many rows tall?
Well, that's the number of Counties for Ohio.
And then how many columns wide?
Well, we have Column B and Column C, so that's two columns qide.
And basically, now, that OFFSET will change every time we choose a new State.
We're back in the VLOOKUP, now we want the second column and then ,False at the end.
It's just like you would normally do with a VLOOKUP.
So Butlerrecorder.oh.gov.
Now, if I would choose a different State-- I know that Alabama also has a Butler county-- so I'll choose Alabama, and you can see that we now get the answer from Alabama, which is different, and the values here changed.
It now starts one row below B1 and there's 67 counties in Alabama.
So every time that I choose a new State, those two numbers down there are going to change.
Let's try Texas.
So it starts in Row 68-- 254 Counties in Texas.
When we choose a valid County from Texas, then the OFFSET will work, the VLOOKUP will work.
Now, if you want to put all this back together-- I show this trick many many times-- I'm down here in H9, I'm going to choose all those cells from the formula bar, except for the equal sign.
Hit Ctrl+C, and then back up here my formula will go to where it has H9 and I'll actually paste that formula there; and then come down here to H10, copy everything except for the form of the bar, Ctrl+C, back up here, H10, Ctrl+V, and now we have one huge monster formula.
And when anyone comes and looks of this they'll say, "Wow, that, you know, that's clearly the most bizarre thing I've ever seen." The real secret is, I always build those monster formulas in several pieces and then paste them all back together at the end.
Okay, so this allows us to look up two different values-- both the State and the County-- we talked about yesterday how to use a concatenated key-- which, frankly, I think it's far simpler-- but if we simply cannot add that extra column in, we don't have control over the data, you can use this OFFSET function as the second argument in the VLOOKUP and everything works alright.
Well, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
