formula for specific text(s) ?
Harry Marnell
cell, another specific item of text is automatically entered in another cell
in same row?
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
dickl
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>...
Lowell R Moorcroft
>Excel 97. How do I format so that if a specific text is entered in one
>cell, another specific item of text is automatically entered in another
cell
>in same row?
>
>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...
>
A simple way would be to set up a lookup table on its own separate worksheet
in your workbook. This table would list cities and corresponding states and
would have to be updated regularly. Because of limits in the chosen lookup
function, my example below needs to list all cities in alphabetical order.
You can accomplish this by highlighting the lookup table and sorting by city
in the first column. The states in the second column don't have to be
sorted, because they will fragment anyway.
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
.
Lowell R Moorcroft
>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