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

Insert_Key

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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.

1627444524422.png


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! (y):)(y)

Andrew
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,478
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi,

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


MrExcel - dependent drop down lists.xlsx
ABCDEFGHIJK
1Item IDSub-ItemConcatenated1st_lov2nd_lovData validation 1Data validation 2
2A011A01/1A01A01/1A01A01/2
3A012A01/2A02A01/2
4A013A01/3A03A01/3
5A014A01/4A04A01/4
6A021A02/1A05 
7A022A02/2  
8A031A03/1  
9A041A04/1  
10A051A05/1  
11A052A05/2  
12A053A05/3  
help
Cell Formulas
RangeFormula
G2:G12G2=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($G$1:G1,$A$2:$A$12),0)),"")
H2:H12H2=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:C12C2=CONCATENATE(A2,"/",B2)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
J2List=$G$2:$G$12
K2List=$H$2:$H$12
 
Solution

Insert_Key

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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! (y) :) (y)
 

Forum statistics

Threads
1,141,074
Messages
5,704,150
Members
421,330
Latest member
eiksnamra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top