Validation - List boxes? Example provided.

[indomfp - ExcelForums.com]

I need some help in using excel, particularily the validation command
I am not a beginner, but am no where near an advanced level user.
The issue I am having is creating a list box that is contingent o
the preceding list box I am unsure if this can be done in excel but
am trying to build it so that you can pick some information in a lis
box based on what was in the preceding list box. I do not need t
immedietly go to the next list box, so if you can please help,
greatly appreciate it

Here is what I am trying to do. (Hopefully this is enough detail fo
those of you that are familiar with excel.)
In cells K4 to K8 I have listed 5 countries of origin of vehicles
American, Japanese, German, English, Italian (K4=American
K5=Japanese, and so on to K8 ).

Cells K11 to O17 represent the car companies associated with th
country of origin
In cells K11 to K17,read Ford, Chevy, Saturn, GMC, Pontiac, Mercury
and Lincoln. In these cells the car companies represent the America
cars.
In cells L11 to L17, I have listed Japanese car companies
In cells M11 to M17, I have listed the German car companies
In cells N11 to N17, I have listed the English car companies
In cells O11 to O17, I have listed the Italian car companies

Cells K20 to O24 have the individual car types associated with th
brand listed
In cells K20 to K24, I have listed the American Car
In cells M20 to M24, I have listed the German cars
In cells N24 to N24, I have listed the English cars
In cells O24 to O24, I have listed the Italian cars

Here is what I am trying to do. In cell A3 I have set up
validation, it is a list that has the source from cells K4 to K8, th
countries of origin. I want to construct this sheet so that when
customer clicks on that he want an American Car in cell A3, he the
goes to cell B3 and gets to pick the company he wants that car fro
(I need to set a validation, another list, that represent th
American car companies, cells K11 to K17. After the customer picks
company, he then goes on to cell c3 and is allowed to pick a type o
car, cells K20 to K24. I need to create this so that it works fo
all countries, companies, and types of vehicles. I do not know ho
to do this, so if you guys can help me out please do so.... If yo
would like me to send you the spreadsheet this information is in,
can also do that. Thanks..

 

[Ron De Giusti]

Hello.

What if you had a separate Worksheet in MS-Excel that listed "Countries of
Origin" and "Cars".

I would then make the "Countries of Origin" column on this Worksheet a
"Defined Name" (i.e., "Insert | Name | Define ...") like "Countries of
Origin".

I would also make the "Cars" column on this Worksheet a "Defined Name".

Then, back on the Worksheet where the user can select a car (i.e. a Cell in
a Column where the Column uses Data Validation and it is for a List and the
list is equal to "Cars"), the column next to the column with the car
selected would use the VLookup function to retrieve the country of origin.

Ron

 

[Debra Dalgleish]

You can create dependent data validation lists. There are instructions here:

http://www.contextures.com/xlDataVal02.html

Name the cells that contain the car companies with the country name. For
example, cells K11:K17 would be named American.

Name the cells that contain car names with the country name and "Cars".
For example, cells K20:K24 would be named AmericanCars

In cell B3, use =INDIRECT(A3) as the source
In cell C3, use =INDIRECT(A3 & "Cars") as the source

 

[indomfp]

You can create dependent data validation lists. There are instruction
here:

http://www.contextures.com/xlDataVal02.html

Name the cells that contain the car companies with the country name
For
example, cells K11:K17 would be named American.

Name the cells that contain car names with the country name an
"Cars".
For example, cells K20:K24 would be named AmericanCars

In cell B3, use =INDIRECT(A3) as the source
In cell C3, use =INDIRECT(A3 & "Cars") as the source

Debra, the example that you linked me to states that I can only us
one-word entries in the Named box, can I use small sentences as long a
they match the others? I will try this out later today, and thanks fo
your help...

 

[Debra Dalgleish]

A range name can't contain spaces. You could use underscores to visually
separate the words in the name.

The page also discusses two word items:

http://www.contextures.com/xlDataVal02.html#TwoWord

and the instructions would work for multiple words. A name can contain
up to 255 characters

 

[indomfp]

You can create dependent data validation lists. There are instruction
here:

http://www.contextures.com/xlDataVal02.html

Name the cells that contain the car companies with the country name
For
example, cells K11:K17 would be named American.

Name the cells that contain car names with the country name an
"Cars".
For example, cells K20:K24 would be named AmericanCars

In cell B3, use =INDIRECT(A3) as the source
In cell C3, use =INDIRECT(A3 & "Cars") as the source

Debra,
I do not understand this piece:
'Name the cells that contain car names with the country name and "Cars
'

I do not understand by what you mean by naming the cells with countr
name 'Cars'. Do you mean I should highlight the entire cell (tha
include all the car types from various companies and countries an
label it as 'Cars'? please clarify.....

I named the American cars as AmericanCars and so on and so for with th
other cars, but how would I prevent someone that picks Chevy as th
company from picking a Ford made car?

Sorry for the repeat posts.... Also if you need me to send you th
actual spreadsheet so you get a better idea of what i am doing I can.
thanks

 

[Debra Dalgleish]

If there's a specific range that contains Chevy cars, name that range
Chevy. Do the same for ranges that contain other car types.

In cell C3 use the Data Validation formula: =INDIRECT(B3)

 

[indomfp]

If there's a specific range that contains Chevy cars, name that range
Chevy. Do the same for ranges that contain other car types.

In cell C3 use the Data Validation formula: =INDIRECT(B3)

I actually figured out what you were saying after I thought about it
but nonetheless, thanks for the help, it works perfectly. I need t
figure out how to use it for multiple words and the such. I owe ya..
: