Shaun asks how he can link values in one worksheet to a lookup table in an external workbook. Episode 1082 will show you how.
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.
Basically, we start out with massive amounts of data.
So, how we're going to analyze this.
Well, let's fire up a pivot table and see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Want to send a shout out to a new viewer, Shawn just discovered us on YouTube.
Shawn has a question. He's trying to set up a couple of worksheets, that link to a table and an outside workbook.
Wants to know, if there's any videos that cover that.
Well, Shawn this one here is going to do it So, here's a place where we can enter one of these codes, and you want to go grab a value from a table.
But the problem is, the table is in another workbook.
Well, the best way to do this, to build this formula, is to have both workbooks open.
And, you'll see here, that I have the AllRates.xls file, with the codes and the rates, and what I'm going to do, just that way we can see this, is I'm going to show both workbooks side by side.
So, I'm going to go to View, Arrange All and choose Vertical.
So, we can see those both.
And then here, I'm going to enter a VLOOKUP for it.
=VLOOKUP, and what we want to look up?
We want to go look up that code that we entered.
And now, the lookup table, the table array.
Well, I'm going to come over here to my other workbook, and choose that table array. Notice, that it automatically put the dollar signs in.
So, it becomes an absolute reference, and then we want the second column and of course comma false at the end, meaning that we need an exact match.
All right, so there we go. Now, we'll take a look at this formula that got built.
You know, lots of very tricky things here, as far as, square brackets and the exclamation point.
If there had been a space in the workbook name or in the worksheet name, we would have to surround that with apostrophes, but you don't have to worry about it, that provided you have both workbooks open at the same time.
All right so, now, let's do a little test here.
We're going to choose a different item and sure enough see that the rate comes over.
So, there's D11, 1.05 H10, 1.14 That all works just fine.
Now, here's the big problem we're going to have.
We're going to click Save and close AllRates, and now, when we choose another item, B22, it works just fine. That's because the table is small.
The rule is that this workbook cannot link to more than 10,000 cells in an external workbook.
So, my table, there were small 10 rows, 2 columns, 20 cells, no problem.
But if your table is larger, for example, a thousand rows with 11 columns.
Well, then you've gone over and your VLOOKUP is not going to work, unless, both workbooks are open at the same time.
So, there you have it.
Thanks for stopping by.
Will see you next time for another netcast from MrExcel.
Well, thank for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
So, how we're going to analyze this.
Well, let's fire up a pivot table and see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Want to send a shout out to a new viewer, Shawn just discovered us on YouTube.
Shawn has a question. He's trying to set up a couple of worksheets, that link to a table and an outside workbook.
Wants to know, if there's any videos that cover that.
Well, Shawn this one here is going to do it So, here's a place where we can enter one of these codes, and you want to go grab a value from a table.
But the problem is, the table is in another workbook.
Well, the best way to do this, to build this formula, is to have both workbooks open.
And, you'll see here, that I have the AllRates.xls file, with the codes and the rates, and what I'm going to do, just that way we can see this, is I'm going to show both workbooks side by side.
So, I'm going to go to View, Arrange All and choose Vertical.
So, we can see those both.
And then here, I'm going to enter a VLOOKUP for it.
=VLOOKUP, and what we want to look up?
We want to go look up that code that we entered.
And now, the lookup table, the table array.
Well, I'm going to come over here to my other workbook, and choose that table array. Notice, that it automatically put the dollar signs in.
So, it becomes an absolute reference, and then we want the second column and of course comma false at the end, meaning that we need an exact match.
All right, so there we go. Now, we'll take a look at this formula that got built.
You know, lots of very tricky things here, as far as, square brackets and the exclamation point.
If there had been a space in the workbook name or in the worksheet name, we would have to surround that with apostrophes, but you don't have to worry about it, that provided you have both workbooks open at the same time.
All right so, now, let's do a little test here.
We're going to choose a different item and sure enough see that the rate comes over.
So, there's D11, 1.05 H10, 1.14 That all works just fine.
Now, here's the big problem we're going to have.
We're going to click Save and close AllRates, and now, when we choose another item, B22, it works just fine. That's because the table is small.
The rule is that this workbook cannot link to more than 10,000 cells in an external workbook.
So, my table, there were small 10 rows, 2 columns, 20 cells, no problem.
But if your table is larger, for example, a thousand rows with 11 columns.
Well, then you've gone over and your VLOOKUP is not going to work, unless, both workbooks are open at the same time.
So, there you have it.
Thanks for stopping by.
Will see you next time for another netcast from MrExcel.
Well, thank for stopping by.
We'll see you next time for another netcast from MrExcel.
