alphnumeric if then statements?

[imdavenp85]

Hello,
I am setting up a database template for people who are not Excel trained and
I am attempting to make the input easy for the users as well as myself when I
begin analyzing the data. I am trying to an if-then statement or create a
macro (I know nothing about macros) that will allow me to have the users
select their county for a list validation and the county number appear the
cell to the right of the county name.
like this

County County Number
Excel 67856

Any advice would be helpful.

 

[Pete_UK]

One way of doing this is to have a table somewhere which lists all
your counties in one column and has the county number in the adjacent
column - suppose this occupies cells X1:Y100. Then if you enter the
county in cell A2, put this formula in B2:

=VLOOKUP(A2,X$1:Y$100,2,0)

If the county in A2 is not present in the list in X1:X100 then this
basic formula will return #N/A - you probably want to avoid that for
your users, so change the formula in B2 to this:

=IF(A2="","",IF(ISNA(VLOOKUP(A2,X$1:Y$100,2,0)),"",VLOOKUP(A2,X$1:Y
$100,2,0)))

This will give you a blank if there is no match, but you might like to
change the final "" to some more meaningful message like "please re-
enter".

Hope this helps.

Pete

 

[Loretta]

Try this:
If you sheet won’t take up much space then feel free to use an out of the
way corner to create a drop down and function lookup table but otherwise add
a 2nd sheet on the same workbook for this info.

1.

Country?

65213


2. You will need a table for the drop down list of countries (better not to
have people type in their country name because you will have typos and
misspellings that the lookup won’t recognize). You may hide this info by
using font light light grey. If you use white outline in color so you don’t
overtype it.
1 Please select
2 Afganistan
3 Botswana
4 United States
5 Zambia
6 Other
When you create the dropdown (combo list object) format it to send the
results in Cell1 of your lookup formula.

3. You will also need a table for the lookup function
Results Cell1 Country ID Country Name Country Number
4 2 Afganistan 65211
3 Botswana 65212
4 United States 65213
5 Zambia 65214
6 Other 65215
7 etc-etc 65216

4. In the space next to the Country name of your form or file.. select this
formula:
(lookup_value,lookup_vector,result_vector)
Sample: =IF(B14=0,"",LOOKUP(B14,C14:C19,E14:E19))

This tells the space that will receive the country number to check for
something in Cell1 (B14), if empty, than the country number stays blank. If
it finds a country name then it will look on the Lookup Table to find the
Country number next to it. You can hide this table by changing font to light
grey or white.

All your people have to do is click on the arrow and use the drop down to
select a country.

 

[imdavenp85]

Peter and Loretta

Thank you both for you help, you have saved a lot of people a lot of long
tedious work this summer.

Isaac