0
I am using two Excel workbooks; one is the backend for the other. In Book 1, there are sheets sheets with a $Timeline, $ClientInfo, and $VendorInfo. Book 2 is meant to be a summary record of different elements pulled from Book 1.
Essentially, my intent was to have a key using on our internal client tracking number (listed in Book 1) that would be entered into Book 2, thereby populating all required fields using the lookup
function.
Basically, where B6
is the key, Book 2 is a table with a bunch of variations on;
=LOOKUP(B6, '[
book1
.xlsx]ClientInfo'!$B:$B,'[book1
.xlsx]ClientInfo'!$N:$N)=LOOKUP(B6, '[
book1
.xlsx]VendorInfo'!$B:$B,'[book1
.xlsx]VendorInfo'!$D:$D)=LOOKUP(B6, '[
book1
.xlsx]Timeline'!$B:$B,'[book1
.xlsx]Timeline'!$E:$E)
Currently, no matter what values are entered into B6
(listed values or made up ones), all of the returned lookup
values are for the last entity listed, ergo the lookup is not actually matching anything correctly.
Some of the result vectors contain formulas (more =lookup's), some contain hand entered values; in both cases the yield is the wrong match.
The question then is two part;
- Are there some initial steps I should have known to take to ensure that this kind of interdependence would work? Or perhaps is this one of those situations where you need to enable some feature or setting of the cells, formula, sheet, or workbook to make it work?
- Am I pushing
lookup
further than it is meant to go (i.e. is it only meant for intra-workbook functionality)? If so what would be a better command (i.e. one more suited to inter-workbook functionality)? (How can I translate thelookup
syntax into the proposed command's syntax efficiently?)- I have no VBA abilities, so sticking to Excel formulas would be preferred in answering
Also, if I am doing this all wrong, let me know. My guess is that this should really be in a database (there will be 27 of the Book2s by the end of this).
Why not just use a vlookup?
=vlookup(B6,[Book1]Timeline!$B:$E,3,false)
to return the value in column D? What are you trying to return? multiple values? – Raystafarian – 2012-02-24T15:20:04.370@Ray Using
=VLOOKUP
isn't returning anything, the cell just displays the formula as entered (whether by hand or using theinsert function
dialog) – mfg – 2012-02-24T15:43:50.603If the cell is formatted text that might happen, try hitting F9, but ultimately what are you trying to do with this question? – Raystafarian – 2012-02-24T15:44:32.373
@Ray Tried the F9, which then gave me a #n/a. Ultimately I am trying to make the lookups look up correctly (?). Found the problem and posted an answer; the table was not sorted by the key (B6), but rather another field. – mfg – 2012-02-24T16:34:45.547