No Links were listed yet. Go ahead and share!
Hey, welcome back to the Mr. Excel net cast I'm Bill Jelen. Basically you start out with massive amounts of data, see how were going to analyze this well let's fire up exhibit table and see if we can solve this problem.
Hey, welcome back to the Mr. Excel net cast I'm Bill Jelen. And when I send a shout out to a new viewer Shawn just discovered us on You Tube. Shawn has a question he’s trying to setup a couple of worksheets that link to a table and an outside workbook. He wants to know if there's any videos that cover that. Well Shawn this one here is going to do it, so here is 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 work book, well the best way to do this, to build this format is to have both workbooks open and I—you'll see here that I have the all rates that Excel S file, with the codes and the rates. And what I'm going to do just though where we can see this, is I'm going to show both workbook 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 V lookup for it, equal V lookup and what we want to look up, we want to go look up that code that we enter and now the look up table, the table of ray well I'm going to come over here to my other workbook and choose that table. And I notice that it automatically put the dollars 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 they’ve 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 the worksheet name and we would have to surround that with apostrophize.
But you don’t have to worry about any of that provided you have both workbooks open at the same time. All right, so now let's do a little test here were going to choose a different item and sure enough to see, that the rate comes over. So there is D11 1.05, H10 1.14 that all works just fine, now here is the big problem were going to have. Right click save and close all 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 is small 10 rows two columns 20 cells no problem.
But if your table is larger for example a thousand rows with 11 columns then you’ve gone over and your V lookup is not going to work unless both workbooks are open at the same time. So there you have it thanks to you for stopping and I’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services