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
B6 is the key, Book 2 is a table with a bunch of variations on;
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
lookupfurther 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 the
lookupsyntax 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).