Pull Down Menus

[Paul]

How can I create a series of pull down menus where the 2nd, 3rd and 4th tier
are based on the preceding choices?

I know how the validate command can work, but and not sure how to create a
dynamic way to populate to ensuing tiers based on the previous selection.

In short....you pulldown item 1, which determines what can be pulldowned
from item 2 and so on for 4 levels.

Now the real challenge is on top of this: I need to repeat this
independently about 200 times. So you will have 200 sets of pulldowns all of
which need to be populated.

Thanks

 

[Shane Devenshire]

Hi,

Here are the steps for creating a dynamic data validation list:

1. Create three or more list in the spreadsheet. For example:
A B C
New York Quebec Canada
Seattle Toronto US
Miami Victoria

You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this
last is optional

2. Select the cell where you want the initial validation list, for this
example, D1
3. Choose Data, Validation
4. From the Allow drop-down and choose List
5. In the Source box enter the following formula
=Countries
6. Move to the location where you want the dynamic (changing) list to
appear, say for example E1
7. Choose Data, Validation, List and in the Source box enter the formula
=INDIRECT(D1)

You can also make a self-referential dynamic data validation list.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Paul]

That is an interesting way to approach it using the named range function.
The problem is that my options are not this simple. Do you know a way that I
can use a 'master list' of cities to then generate the cities list based upon
the country chosen. Maybe they choose the Country and press a form button
which would then develop the City list and assign a range name to the list,
and then repeat that for the Street level and address levels?

 

[Gord Dibben]

See Debra Dalgleish's site for more on dependent lists.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP