Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pull Down Menus
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pull Down Menus
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pull Down Menus
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? "Shane Devenshire" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pull Down Menus
See Debra Dalgleish's site for more on dependent lists.
http://www.contextures.on.ca/xlDataVal02.html Gord Dibben MS Excel MVP On Wed, 31 Dec 2008 08:49:01 -0800, Paul wrote: 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? "Shane Devenshire" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Header/Footer Pull Down Menus | Excel Discussion (Misc queries) | |||
pull down menus | Excel Worksheet Functions | |||
hyper links in pull down menus | Excel Discussion (Misc queries) | |||
Pull down menus possible in excel? | Excel Discussion (Misc queries) | |||
how do I set up pull down menus within a cell; excel | Excel Discussion (Misc queries) |