Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
If statements two | Excel Discussion (Misc queries) | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
If/Then Statements | Excel Worksheet Functions | |||
sum and if statements | Excel Discussion (Misc queries) | |||
IF statements | Excel Discussion (Misc queries) |