I need another way to take the contents of two cells (City and State) and go
find whether they exist on another workbook that contains a sheet for each
state. These sheets are each named for a state using a 2-letter abbreviation.
The cities are listed in column A of each sheet. I am currently doing this
using the lookup function that requires I extract the city and the state from
one sheet and place it in a "lookup" sheet. The lookup formula then sees if
the named city is located in the named state (sheet) and returns either a
"Not Found" or "HiDTA Area". An additional formula puts a 1 or 0 in a cell
based upon the Not Found/HiDTA answer. This 1 or 0 is then entered in the
first sheet (where the city and state were originally entered).

However, when I have completed the lookup I save the sheet using a unique
name and when I reload that sheet the lookup fails, even if I have the
original "lookup workbook" loaded as it points to a specific sheet.

Is there another way to lookup to see if a given city/state combination
exist? I need this to work even afterthe original workbook is saved (without
the additional 50+ sheets) and reopened.

Trying to make this work smoothly and consistently is causing great
frustration. I will have 8 people using this workbook once I solve this
problem.

=VLOOKUP(A1,INDIRECT(B1&"!"&"A:A"),1,0)
where A1 contains the city name and B1 is the state
you can nest this into another function to determine if the value is found

=NOT(ISERROR(VLOOKUP(A1,INDIRECT(B1&"!"&"A:A"),1,0 )))
which returns TRUE for found and FALSE for not found.

or nest it with an IF statement to do whatever you need.

"David Vollmer" wrote:

I need another way to take the contents of two cells (City and State) and go
find whether they exist on another workbook that contains a sheet for each
state. These sheets are each named for a state using a 2-letter abbreviation.
The cities are listed in column A of each sheet. I am currently doing this
using the lookup function that requires I extract the city and the state from
one sheet and place it in a "lookup" sheet. The lookup formula then sees if
the named city is located in the named state (sheet) and returns either a
"Not Found" or "HiDTA Area". An additional formula puts a 1 or 0 in a cell
based upon the Not Found/HiDTA answer. This 1 or 0 is then entered in the
first sheet (where the city and state were originally entered).

However, when I have completed the lookup I save the sheet using a unique
name and when I reload that sheet the lookup fails, even if I have the
original "lookup workbook" loaded as it points to a specific sheet.

Is there another way to lookup to see if a given city/state combination
exist? I need this to work even afterthe original workbook is saved (without
the additional 50+ sheets) and reopened.

Trying to make this work smoothly and consistently is causing great
frustration. I will have 8 people using this workbook once I solve this
problem.