1

I am trying to make a 3 way dependent dropdown in Excel, that is dynamically generated from ONE source table that may be unsorted. I have found a lot of tutorials online that involve blowing out the source table into a bunch of smaller tables and using named ranges, which will not work for me as additional data will be added to the source and the dynamic dropdowns need to automatically update as needed.

For example: Excel screenshot The list of models table is what pairs each model to its manufacturer and category. This table will always be growing.

The asset tracking table is where we will track who in the company has what hardware asset, so this table is always expanding too. The yellow section is where the dropdowns should be, where the manufacturer is dependent on the category, and then the model is dependent on the manufacturer.

So for example, if I put in Adapter in the Category, then the dropdown in Manufacturer will only show manufacturers that make items in that category. So, only Dell and Ugreen would show up. Then after selecting Dell for example, then when I click on the Model dropdown, it will only show models that are made by Dell in the Adapter category.

I do programming, so I can explain in code speak what I want to happen:

(In manufacturer cell)

  1. Look at cell to the left and see what category it is -
  2. select Manufacturers from 'ModelList' where category = cell to the left
  3. Display those results in a dropdown

(in model cell)

  1. Look at the cell to the left and see what manufacturer it is
  2. Looks 2 cells to the left to see what category it is
  3. select models from 'ModelList' where manufacturer = cell to the left AND category = 2 cells to the left
  4. Display those results in a dropdown

Then like I said, these rows will be repeated, so we cant have a singular lookup table as there will be many rows of data validation

I am using Office365 so we can use all the newest formulas to make this the easiest. I'm also okay with using VBA but I don't know VBA coding.

Mark Reddington
  • 123
  • 10
  • line 5,20,43,46 in sheet2 have empty 2nd & 3rd column.. is these 'blank' entries intended to be part of the "valid option"? – p._phidot_ Jul 16 '21 at 17:25
  • @p._phidot_ correct, some will be blank. Model category will never be blank, but manufacturer and model both may be blank. – Mark Reddington Jul 16 '21 at 17:31
  • You can create a spill range somewhere for the first drop-down range: `=SORT(UNIQUE(FILTER(1stRange,1stRange<>"")))` then name it and refer to it in the first drop-down. Then create ranges the same way, but refer to the choice of the first drop-down. Name it and refer to in second drop-down. Etc. – P.b Jul 17 '21 at 17:26
  • @P.b That makes no sense. That will pull the unique items from the model category which is good for the first dropdown, but then how would i 'refer to it' in the second dropdown? thats that part that doesn't work. Since i would only need to pull in manufacturers that make items in that category, i would need to take the first dropdown selection and find all rows in the source table where category = dropdown 1. Then from there for Model I would need to pull all rows where category = dropdown 1 and manufacturer = dropdown 2 – Mark Reddington Jul 19 '21 at 21:47
  • Refer to the cell of the choice of your drop-down(s) to have your data filtered: `=UNIQUE(FILTER(2ndRange,1stRange=1stDropDownChoice))` – P.b Jul 20 '21 at 07:55
  • @P.b doesn't work like that. you cannot have a filter function in a data validation list. – Mark Reddington Jul 22 '21 at 19:56
  • Darn - is this question still unresolved? see answer below. – JB-007 Jul 22 '21 at 21:02
  • @MarkReddington - will wait for you to revert re: my latest /revised soln (which was done shortly before I saw your updated Q - which is looking **much** clearer on face of it .. ta :) – JB-007 Jul 23 '21 at 23:42

1 Answers1

2

Screenshot / here refer: (Revised/reconsidered in line of previous role

Screenshot - data (Sheet 1), lists (Sheet 1 & 3 ), and 'universe' data (Sheet 2)

VALIDATION LISTS

With same arrangement per screenshot above, two key formulas for respective 'manufacturer' and 'name/model' lists as follows:

Manufacturer

=IF(D5="","",TRANSPOSE(UNIQUE(FILTER(Table14[MANUFACTURER],Table14[MODEL CATEGORIES]=D5))))

Model

=IF(D5="","",TRANSPOSE(FILTER(Table14[NAME],1*(Table14[MODEL CATEGORIES]=D5)*(Table14[MANUFACTURER]=E5))))

SPACING

To accommodate potentially lengthy validation lists, proposal is to either create 2 additional tabs or, assuming adequate space to right of 'data' (sheet 1 in your case), you can follow setup per screenshot for 'Manufacturer'

FEATURES

  1. Completely dynamic (even for validation list 'sources' outside of data table)
  2. Validation lists reference adjacent 'transposed/horizontal' values after own selection has been made
  3. All validation lists driven by 'universal' data set (AO:AQ, screenshot)
  4. Flow as follows: Select Category using validation lists that reference table; once approved change implementing; re monitoring - some people are
JB-007
  • 2,156
  • 1
  • 6
  • 22
  • I still need to test this, but I think I am seeing an issue. This is good for one row, but the question at hand is that in my data entry sheet will have multiple rows (i.e. multiple hardware assets tracked), so we would have to have a lookup range for each row, which doesn't work. For example, add another row to your yellow section and try it out. The yellow will be always growing as new assets are added. – Mark Reddington Jul 22 '21 at 21:33
  • There's 2 sheets that will always be growing, the list of all the hardware models, then the sheet listing who has what hardware asset. So basically, there's needs to be infinity rows of the dropdowns so I can go, ok Bob Smith just got assigned a Headset, Plantronics, Blackwire. Then John Doe just got assigned a Keyboard, Microsoft, Standard USB. – Mark Reddington Jul 23 '21 at 06:02
  • I reformatted the entire question, hope it makes more sense now – Mark Reddington Jul 23 '21 at 23:24
  • Thanks - but we may have crossed wires - i.e. I've just updated/completely revised my soln! :) – JB-007 Jul 23 '21 at 23:40
  • YES!!! This is EXACTLY what I was looking for!! Tested and appears to be working as expected! Thank you so much!! I had a feeling transposing would take place because I messed around with that during my testing. Just one small adjustment I am trying to make - lets say in UNIVERSE DATA, sheet 2, I add "Cables" in the category but leave the manufacturer and name blank. This returns "0", how do I make it return N/A? – Mark Reddington Jul 24 '21 at 00:00
  • What a relief ! thumbs up/down to aid future users. ta (your point balance is sufficiently high by now I believe)? – JB-007 Jul 24 '21 at 06:48
  • aHHH - as for your Q: you'd need to make formula a bit longer (~double length). If not an issue, then simply use =IF(formula I have used = 0, 'n/a', formula I have used) – JB-007 Jul 25 '21 at 11:12
  • Only (minor) issue now is that if there is genuinely an item called '0' (unlikely, but humour me), this would return n/a. – JB-007 Jul 25 '21 at 11:14
  • Some additional issues I ran into: If I inserted a new row in the middle of my data, all the lookup formulas would get messed up due to referencing. The solution was to use INDIRECT on the lookup list forumulas: =IF(IF(INDIRECT("List!E"&ROW(A2))="","",TRANSPOSE(UNIQUE(FILTER(Table5[Manufacturer],Table5[Model Categories]=INDIRECT("List!E"&ROW(A2))))))=0,"N/A",IF(INDIRECT("List!E"&ROW(A2))="","",TRANSPOSE(UNIQUE(FILTER(Table5[Manufacturer],Table5[Model Categories]=INDIRECT("List!E"&ROW(A2))))))) – Mark Reddington Aug 05 '21 at 21:21