Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
David Vollmer
 
Posts: n/a
Default Another way to lookup data

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.

  #2   Report Post  
JMB
 
Posts: n/a
Default

=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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Saving data in a worksheet within a workbook Homeuser Excel Discussion (Misc queries) 2 August 21st 05 10:49 PM
Combining data from worksheets - lookup? Connie Excel Discussion (Misc queries) 3 August 18th 05 07:46 PM
auto fill data into a cell from a lookup table Tetradpoint Excel Discussion (Misc queries) 1 April 19th 05 04:46 PM
Lookup Access data in Excel Chris Kellock Excel Worksheet Functions 1 December 28th 04 02:51 PM


All times are GMT +1. The time now is 01:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"