Creating multiple (5) drop down lists, all dependent on the selection(s) of the prior

[Andrea.Taylor]

I need to put together a worksheet that assists users in selecting a valid account for posting activity. Prior to viewing a list of acceptable account numbers (with full descriptions), the user must 1st select from 4 other drop downs.

Column A has 2 possible selections
Column B has 2 possible selections
Column C has 8 possible selections
Column D has 20 possible selections
Column E has 36 possible selections

Column A and B are not dependant on each other
Column C is dependant on the combined selections in Col A and B
Column D is dependant on the combined selections in Col A, B and C
Column E is dependant on the combined selections made in Col A, B, C and D

I've read many posts that reference cascading drop down lists. I have not been able to find a post that 1) addresses having more than 2 dependant lists, nor 2) avoiding the need to create dependent lists (using the Name and Indirect function) for each possible combination

The Data I'm building off of is not static though major changes are not expected to occur often.

I'm pretty lost here. I'd greatly appreciate any help.

Thanks

 

[pareshj]

It is very simple to create dependent drop down list in excel. Check out the below link and follow the steps,


Create Dependent Drop-down lists in excel | S&P Infotech Blog

 

[kaustubhjadoun]

Pareshj, Query here is different. Pls cross-check and provide a solution is any..

 

[kaustubhjadoun]

Hello Andrea,
It's been 7 years you raised this query. Could you obtain any solution?
I have landed into a similar situation where I need more than 2 dependent dropdowns.
e.g. COUNTRY >> CUSTOMER >> LOCATION >> PRODUCTION LINE >> MACHINE no.
How to do it??

 

[kaustubhjadoun]

I need to put together a worksheet that assists users in selecting a valid account for posting activity. Prior to viewing a list of acceptable account numbers (with full descriptions), the user must 1st select from 4 other drop downs.

Column A has 2 possible selections
Column B has 2 possible selections
Column C has 8 possible selections
Column D has 20 possible selections
Column E has 36 possible selections

Column A and B are not dependant on each other
Column C is dependant on the combined selections in Col A and B
Column D is dependant on the combined selections in Col A, B and C
Column E is dependant on the combined selections made in Col A, B, C and D

I've read many posts that reference cascading drop down lists. I have not been able to find a post that 1) addresses having more than 2 dependant lists, nor 2) avoiding the need to create dependent lists (using the Name and Indirect function) for each possible combination

The Data I'm building off of is not static though major changes are not expected to occur often.

I'm pretty lost here. I'd greatly appreciate any help.

Thanks

Hello Andrea,
It's been 7 years you raised this query. Could you obtain any solution?
I have landed into a similar situation where I need more than 2 dependent dropdowns.
e.g. COUNTRY >> CUSTOMER >> LOCATION >> PRODUCTION LINE >> MACHINE no.
How to do it??

 

[dalford]

Hi all,

I recently had a similar issue in line with the old discussions and recent question:
- several cascading dependent drop down menus
- the final dropdown menu dependent on its parent menu, AND
- one additional 'independent' dropdown menu

The menu options stored in a series of tables (one for each level), using the OFFSET MATCH COUNTIF setup - as opposed to the INDIRECT approach - this is intended as an easily adaptable template for use in several contexts, so this is the best setup for the purpose.

The essentials of this setup are described here:
https://www.excelcampus.com/tables/dependent-drop-lists/
I format the data ranges as tables and created Named lists so that it is more easily adaptable.


1) My first approach for the final dropdown was to use COUNTIFS to match the two dropdown menus to the two columns in the final table i.e. something structured as follows:
=OFFSET(EMERGENCY_START,MATCH(N4&O4,EMERGENCY_COL&DOMAIN_LIST,0),2,COUNTIFS(EMERGENCY_COL,"="&N4,DOMAIN_LIST,"="&O4),1

This worked fine until i reopened the file - i would have to 'reenter' the data validation formula (just opening Data Validation, clicking on the formula, and clicking OK) for the dropdown menu to work. Given that this file was to be locked and circulated to various individuals for inputs, this was not going to work.

2) The second approach drew on a 'hidden' concatenated column, and a corresponding column in the lookup table i.e.
- a hidden column: =[@PCODE3]&[@[Activity location]]
- a corresponding column in the relevant lookup table: =[@[Admin3 Col]]&[@[SiteType Col]]

and thus we can revert to the more traditional data validation formula structured as follows:
=OFFSET(ADMIN3_START,MATCH(AI4,ADMIN3_COL,0),4,COUNTIF(ADMIN3_COL,AI4),1)

I've found this to be stable, though it did add 0.2 Mb to the file compared to the COUNTIFS approach.

Hope this helps!

 

[valleygirl2day]

Hi All,
I have successfully created a spreadsheet with 5 dependant drop lists that uses Name Manager, Data Validation and VBA Code
The Workbook is only 65kb
The workbook contains 2 sheets
Sheet 1
Trees
Sheet 2
TreeNames

The sheet with the pick-lists is called Trees
The sheet with the data table is called TreeNames, the sheet containing 5 columns of data with 290 rows of tree information, 4 columns contain multiples and the last has unique data.

You will need to set up 10 Ranges in Formulas - Name Manager
They are very similar with only a few minor changes between each range
e.g.
List_TreeFamily
=OFFSET(TreeNames!$A$3,0,0,COUNTA(TreeNames!$A$3:$A$999))
List_TreeFamilyD
=OFFSET(TreeNames!$G$3, 0, 0, COUNT(IF(TreeNames!$G$3:$G$999="", "", 1)), 1)
List_TreeGenus
=OFFSET(TreeNames!$B$3,0,0,COUNTA(TreeNames!$B$3:$B$999))
List_TreeGenusD
=OFFSET(TreeNames!$H$3, 0, 0, COUNT(IF(TreeNames!$H$3:$H$999="", "", 1)), 1)
List_TreeSpecies
=OFFSET(TreeNames!$C$3,0,0,COUNTA(TreeNames!$C$3:$C$999))
List_TreeSpeciesD
=OFFSET(TreeNames!$I$3, 0, 0, COUNT(IF(TreeNames!$I$3:$I$999="", "", 1)), 1)
List_TreeCommon
=OFFSET(TreeNames!$D$3,0,0,COUNTA(TreeNames!$D$3:$D$999))
List_TreeCommonD
=OFFSET(TreeNames!$J$3, 0, 0, COUNT(IF(TreeNames!$J$3:$J$999="", "", 1)), 1)
List_TreeOther
=OFFSET(TreeNames!$E$3,0,0,COUNTA(TreeNames!$E$3:$E$999))
List_TreeOtherD
=OFFSET(TreeNames!$K$3, 0, 0, COUNT(IF(TreeNames!$K$3:$K$999="", "", 1)), 1)

On Sheet 1 Trees
Create your pick-lists
e.g.
Pick-list Column B - Family
Data = Data Validation - List
=List_TreeFamilyD
Pick-list Column C - Genus
Data = Data Validation - List
=List_TreeGenusD
Pick-list Column D - Species
Data = Data Validation - List
=List_TreeSpeciesD
Pick-list Column E - Common
Data = Data Validation - List
=List_TreeCommonD
Pick-list Column F - Other
Data = Data Validation - List
=List_TreeOtherD

To make each pick-list work the sheet needs to use VBA code
VBA Code for sheet (Trees) with pick-lists is below

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Sheets("TreeNames").Range("M3") = Sheets("Trees").Range("B" & ActiveCell.Row).Value
Sheets("TreeNames").Range("N3") = Sheets("Trees").Range("C" & ActiveCell.Row).Value
Sheets("TreeNames").Range("O3") = Sheets("Trees").Range("D" & ActiveCell.Row).Value
Sheets("TreeNames").Range("P3") = Sheets("Trees").Range("E" & ActiveCell.Row).Value

End Sub

Sheet 2 TreeNames
The spreadsheet with the data table has the data is Columns A:E
e.g.
Family Genus Species CommonName OtherName
Adoxaceae Vibernum Vibernum tinus Vibernum Other Name 1
Anacardiaceae Schinus Schinus molle Pepper Tree Other Name 2
Anacardiaceae Schinus Schinus sp Peppercorn Other Name 3
Apocynaceae Nerium Nerium oleander Oleander Other Name 4

Column A
Family
Column B
Genus
Column C
Species
Column D
CommonName
Column E
OtherName

This sheet also contains columns to extract the Unique names for each column as there are multples
r.g.
Column G
Distinct Family
=IFERROR(INDEX(List_TreeFamily,MATCH(0,COUNTIF($G$2:G2,List_TreeFamily),0)),"")
Column H
Distinct Genus
=IFERROR(INDEX(List_TreeGenus,MATCH(0,COUNTIF($H$2:H2,List_TreeGenus)+(List_TreeFamily<>$M$3),0)),"")
Column I
Distinct Species
=IFERROR(INDEX(List_TreeSpecies,MATCH(0,COUNTIF($I$2:I2,List_TreeSpecies)+(List_TreeGenus<>$N$3)+(List_TreeFamily<>$M$3),0)),"")
Column J
Distinct Common
=IFERROR(INDEX(List_TreeCommon,MATCH(0,COUNTIF($J$2:J2,List_TreeCommon)+(List_TreeSpecies<>$O$3)+(List_TreeGenus<>$N$3)+(List_TreeFamily<>$M$3),0)),"")
Column K
Distinct Other
=IFERROR(INDEX(List_TreeOther,MATCH(0,COUNTIF($K$2:K2,List_TreeOther)+(List_TreeCommon<>$P$3)+(List_TreeSpecies<>$O$3)+(List_TreeGenus<>$N$3)+(List_TreeFamily<>$M$3),0)),"")

To Make it all work the values are then stored and changed upon each pick-list selection in columns M:P
Column M
Selected Family
VBA Code updates value upon selection from Trees - Family Pick-list
Column N
Selected Genus
VBA Code updates value upon selection from Trees - Genus Pick-list
Column O
Selected Species
VBA Code updates value upon selection from Trees - Species Pick-list
Column P
Selected Common Name
VBA Code updates value upon selection from Trees - Common Name Pick-list

Hopefully this will help someone in the future