entering data from a list

H

Harley Rider

I am trying to build a form with certain blocks that will be filled by the
user making a choice from a list of options and reasons for thoses options on
another sheet. Example would look like this

Sheet 1 (The main form)
______ Please pick from the list what State you are from (When they click on
this box, it will give them the choices and reason from sheet2)


Sheet 2
Col 1 Col 2
Data Reason for selection
8 Select this one if you are from CA
9 Select this one if you are from FL
10 Select this one if you are from MA

I don’t want the use to be able to enter any thing other than their choice
from the options given. When they make their selection, the number from
sheet2, column 1 would be entered on to sheet 1. Can anyone help me with
this…Any help would be greatly appreciated
 
H

Harley Rider

I would of thought some Excell experts out there would of told me how to do
this by now! Can anyone HELP ME? I really need to get this form done and my
head is bleeding from all the bangging it in to the wall!!!
 
A

Ardus Petus

Your list in Sgeet2 should have 1 column only

You must assign a name (eg: States") to it
(Insert>name)

Get back to sheet1
Select rows to be validated by drop-down list
Data>Validation
Select List
Enter =States in range box

Et voila!

HTH
 
H

Harley Rider

Sir, Thank you for our reply and suggestion however I am required to have
two columns because I do not want the reason for making the selection to be
enter on to the form. I need the use to select/make a choice from column one
due to the info in column two. I would appreciate any other thoughts or
ideal you might have.
 
A

Ardus Petus

Sorry, I see no solution.

Cheers,
--
AP

Harley Rider said:
Sir, Thank you for our reply and suggestion however I am required to have
two columns because I do not want the reason for making the selection to be
enter on to the form. I need the use to select/make a choice from column one
due to the info in column two. I would appreciate any other thoughts or
ideal you might have.
Click to expand...
 
E

Eddy Stan

Hi there,
I don't really understand what u are looking at:
You have 2 sheets
Sheet 1 - given to the user to choose his input from validation list (code &
city)
Based on his choice (code / city) you need to pick up the reason from sheet2
and display next to city like below:
B4 C4 D4
Code city Reason
2 City 02 Reason 08
Put at D4
==INDEX(Sheet2!$D$4:$D$22,MATCH(1,(Sheet2!$B$4:$B$22=Sheet1!B5)*(Sheet2!$C$4:$C$22=Sheet1!C5),0))
array formula so : Ctrl+ shift +enter
in sheet2 you will have code, city & reason listing from range b4:d22
in sheet2 you will have code 1,2,3 range name as code
in sheet2 you will have city city 01, city 02, city 03
in sheet2 you will have reason 01.. reason 18

Now you can copy the b4:d4 down the rows for user to select...
 
Top