Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default alphnumeric if then statements?

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default alphnumeric if then statements?

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

On May 29, 6:23*pm, imdavenp85
wrote:
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.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default alphnumeric if then statements?

Try this:
If you sheet wont 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 wont recognize). You may hide this info by
using font light light grey. If you use white outline in color so you dont
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.

--
Thanks!
Loretta


"imdavenp85" wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default alphnumeric if then statements?

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
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
If statements two Jason Excel Discussion (Misc queries) 3 January 3rd 08 07:29 PM
IF Statements (Mutliple Statements) Deezel Excel Worksheet Functions 3 October 19th 06 06:13 AM
If/Then Statements MrExcelWannaB Excel Worksheet Functions 2 May 26th 06 04:44 PM
sum and if statements jimk Excel Discussion (Misc queries) 2 August 19th 05 11:02 AM
IF statements y_not Excel Discussion (Misc queries) 4 August 9th 05 04:35 PM


All times are GMT +1. The time now is 10:53 PM.

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

About Us

"It's about Microsoft Excel"