E.G. If I enter "Los Angeles" in a Column B cell, "Calif" will be entered
in Column E. Or if I enter "Las Vegas" it enters "gambling" or
whatever...
TIA
Enter this formula in any cell of your choice:
=IF(C4="Los Angeles","Calif")
and then type Los Angeles in cell C4.
regards
//dickl.
Harry Marnell wrote in message <7c6ojf$rh5$1...@news.snowcrest.net>...
Set up a table resembling the following, in 2 columns, on a separate
worksheet:
Amarillo TX
Houston TX
Los Angeles CA
Port Arthur TX
San Francisco CA
Highlight the table and choose Insert Name, then enter a name in the box,
i.e. "StateFind"
Go to your entry sheet and highlight the column in which cities will be
entered, and choose Insert Name, i.e., "CityGiven".
In each cell into which you want the state automatically placed, put this
formula:
=IF(ISERROR(VLOOKUP(CityGiven,StateFind,2,FALSE)),"(City
Unavailable)",VLOOKUP(CityGiven,StateFind,2))
This will return the appropriate state if the city is found. If the city
isn't found, the phrase "(City Unavailable") will be placed in the state
cell. This isn't particularly aesthetic, however, so if you want to be
fancier, you can cause a message box to come up if an unlisted city is
entered, by doing this:
Type =VLOOKUP(CityGiven,StateFind,2) into the state cell
Choose Data Validation/Custom
Type =VLOOKUP(CityGiven,StateFind,2) into the Formula cell
Choose the tab "Error Message"
Choose the error message type (stop, warning, information)
Type in the desired error message, such as "City not listed in table. Update
the table and then re-enter the city on this sheet".
Click OK
When you or someone else types in the city, the error message should display
(if I've done this right!)
but the entered data unfortunately will remain, no matter what style of
error is chosen.
Hope this approach helps. Cheers!
Lloyd Moorcroft
lmoo...@dnai.com
.
When sorting the table by city, the *entire* table including the column of
desired return values needs to highlighted - else the city column will sort
independently of the adjacent state (or whatever) column in the table ~ not
helpful!
Also, the first instruction in the error message box section should not have
"Type =>" in it ~ the instruction means to tell just you to
Choose Data Validation/Custom
Also, when in the error message section above I said type
=VLOOKUP(CityGiven,StateFind,2)
into the state cell, I meant the state cells in your data entry sheet, *not*
the state cells in the table.
Lastly, when I said
When you or someone else types in the city, the error message should
display
I meant, of course, the message would display *if* the typed-in city wasn't
available in the table.
Sorry to the list for babbling on...and on...
Lloyd Moorcroft
lmoo...@dnai.com