Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookups using 2 workbooks
I have a main workbook and a lookup workbook. The main workbook has among
other things 2 cells: one that contains a city and one that contains a state. If a city and corresponding state are a match then a cell in the main workbook will receive a 1; if no match, then a 0. The lookup workbook has sheets for each state (plus VI and PR). I have figured out how to do the lookup if I use the lookup sheet to pull the city and state data into it. It does a "local" lookup and places a number (1 or 0) in a cell on that workbook that the main workbook reads and places the same number in a cell. The formula that I use and is contained on the lookup workbook is: =IF(ISERROR(VLOOKUP($B$1,INDIRECT($B$2&"!A2:C1500" ),3,FALSE)),"Not Found","HiDTA Area"). This works fine as long as I get the city and state from the main workbook and then the main workbook gets its answer (a 1 or 0) from the lookup workbook. B1 contains the city and B2 contains the state information. The formula goes to the proper state sheet and looks for the city using the range of A2:C1500 on each sheet. If found it will put a 1 in a cell (C3) on the lookup workbook which is then read from the main workbook. If I put the 2 workbooks into one I can make it work with no problem. However, I have to save each of the workbooks and they are 2MB in size when put together but the main workbook is less than 100KB if I keep them separate. The reason I am having a problem is that when the workbooks are saved they are, of course, saved with a unique name. If I open them later and try change the city and/or state, the lookup workbook won't recognize the "new named" workbook and the lookup fails. It is expecting the master main workbook. I probably have not made myself as clear as I should but maybe someone will understand. Your help is most appreciated! Thank you, David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookups and ifs! | Excel Worksheet Functions | |||
Help with LOOKUPS | Excel Worksheet Functions | |||
Lookups??? | Excel Worksheet Functions | |||
Lookups ref to other workbooks in locked cells | Excel Discussion (Misc queries) | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions |