Dynamic list that is conditional on value selected in another dynamic list?

[Insert_Key]

Hi there ?

I am seeking some help for a solution which I am sure somebody has a very simple solution to – I just can’t figure it out for myself.

A very simplified example of what I’m trying to achieve (and my issue) is below:

1. Column A contains a sequential list of alphanumeric Item IDs (e.g. A01, A02), no blanks.
2. Item IDs may, validly, appear in the list more than once (e.g. A01, A01, A01).
3. Column B contains a numerical value for Sub-Item IDs (e.g. 1, 2, 3), no blanks.
4. Column C concatenates adjacent cells from columns A and B to provide a unique identifier (e.g. A01/1, A01/2, A01/3) for Sub-Items.
5. A dynamic dropdown in F2 allows a user to select from a list of Item IDs to populate a range of cells with data relevant to that ID. Each Item ID is only listed once.
6. A dynamic dropdown in F3 allows a user to select from a list of Sub-Item IDs to populate a range of cells with relevant data to that Sub-Item.

Both dropdowns are driven by formulas entered as a Source for Data Validation Lists and everything described above works fine. What I haven't been able to resolve is functionality to constrain the list at Step 6 to be conditional upon the selection made at Step 5, for example: if A01 is selected I want the user to only see Sub-Items starting with A01 (e.g. A01/1, A01/2, A01/3) and no others (e.g. not A02/1, not A05/3 etc.).

It would make sense to me that my formula in the Source for the List in F3 could be enhanced to limit the Sub-Item list to display only those that start with the Item ID selected in F2, but I’ve found it much harder than anticipated and haven’t got anything close to a working solution.

I am using a 2016 version of Excel and it does not include the FILTER function, which looks like it may have been helpful to me. I do not know VBA and while I can copy and paste somebody’s solution, I have a preference for a front end solution that I can fix if I break it!

Thanks in advance!

Andrew

 

[jorismoerings]

Hi,

If you're willing to accept helper columns, try this:

MrExcel - dependent drop down lists.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Item ID	Sub-Item	Concatenated				1st_lov	2nd_lov		Data validation 1	Data validation 2
2	A01	1	A01/1				A01	A01/1		A01	A01/2
3	A01	2	A01/2				A02	A01/2
4	A01	3	A01/3				A03	A01/3
5	A01	4	A01/4				A04	A01/4
6	A02	1	A02/1				A05
7	A02	2	A02/2
8	A03	1	A03/1
9	A04	1	A04/1
10	A05	1	A05/1
11	A05	2	A05/2
12	A05	3	A05/3
help

Cell Formulas
Range	Formula
G2:G12	G2	=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($G$1:G1,$A$2:$A$12),0)),"")
H2:H12	H2	=IFERROR(INDEX($C$2:$C$12, MATCH(0, COUNTIF($H$1:H1, $C$2:$C$12)+($A$2:$A$12<>help!$J$2), 0)),"")
C2:C12	C2	=CONCATENATE(A2,"/",B2)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J2	List	=$G$2:$G$12
K2	List	=$H$2:$H$12

 

[Insert_Key]

I've actually got an entire, hidden, helper sheet in my workbook. Columns A - C in my sample are on this hidden sheet, but the two lists are on a user-viewable page - so no issue with helper columns at all! It hadn't entered my mind to use a helper column to create a sub-list to point the Data Validation at.

I modified your formula to suit my workbook and it worked great. Initially I'd set the CSE ranges in G2 and H2 to look down entire columns (my range not being set and is expected to grow) but figured out pretty quickly that it made things grind to a halt, so set it to a more modest number of rows that still allowed for some headroom/growth. For the data validation, I utilised on what I had in my item selection (J2) as it uses OFFSET, IF and MAX to provide a neat list without any blanks selections available in the dropdown.

To be honest, I'm struggling to get my head around how your formula is set up in H2 but I don't expect you to explain it and it's working as a solution flawlessly. Thank you very much!