D
David Vollmer
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
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