Large Dependent Data Validation, 1 Range, Multiple Values


New Member
Nov 17, 2016
Hello All,

I have UTFSE however i really am having difficulty explaining this issue so please bear with me.

I have a list of products and its used for Data Validation, the idea is i can just select the product name, the next step is to be offered a list that will only show me the relevant product options for each product.

basically the issue im running into is 1 product can have multiple options, so Product X can come in Size A and Size B, but Product X In Size A Comes from supplier1, Product X In Size B comes from supplier2 and Product X Can come in Both Size A and B from Supplier3.

So i have been using named ranges, merged cells and Data validation for this, below is what my setup looks like

Column A
Product X
Product Y
Product Z

The above list is in a work book on its own, its just the list that allows me to select the product and then based on my selection i want it to show my the relevant product options (Size A or Size B or Both) - the idea here is to limit user error by only allowing real product combination (Not combinations that dont exist, like product x of supplier2 in size A)

So here is where it gets tricky, on a different workbook i have more Data validation lists and they look like this

Column A Column B
1 - Product X - Size A

2 - Product Y - Size B

3 - Product Z - Size A
4 - (Merged) - Size B

I thought i was being clever merging rows 3 & 4 for Product Z as it has mutiple choices, but an issue i run into here is that automatically defining names using the data on the left does not apply a range to row 4 in this example, that has to be done manually, so another and i think better idea is to have repeating info, so in the instance of just Product Z it would go like this:

Column A Column B
1 - Product X - Size A

2 - Product Y - Size B

3 - Product Z - Size A

- Product Z - Size B

But then im going to get the same products appearing in the same list but returning different values which will also cause big issues, so at this point im stumped, especially as this is just the first hurdle because then i have to so the same again with the suppliers which makes it much more complicated

Column A Column B Column C
Product X - Size A - Supplier 1
- Supplier 2

Product Y - Size B - Supplier 2

Product Z - Size A - Supplier 2
Supplier 3
Product Z - Size B - Supplier 2
Supplier 3

Finally in the above example i didnt include row numbers as they will change depending on the solution, i could merge the cells but merged cells dont work in Tables and dont work with automatically defined names (which is a must as the data sets are HUGE in my actual real sheet im working on) OR if i don't used merged cells i will have to find away of preventing duplicate entries appearing in the list, i need one entry (product X) to appear in the list with multiple values without using merged cells, this will also allow me to use a table (Ctrl-t type of table) whic is great as those types of table automatically increase a range when a new entry is added, so tables are definitely the preferred way of storing the data.

****it im finding this hard to explain, so if you guys want a video of what i mean or a screenshot i can make one or something, just let me know, if you do indeed need a workbook im sure i can cook up an example too, but ANY help would be great, i also forgot to say, Im giving everything in collum B a range name made automatically from the same row but in Collum A, im then using INDIRECT to create the dynamic reference so my lists range is correct.



Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.


Well-known Member
Oct 17, 2005
Office Version
  1. 365
  1. Windows
Hi Corin,

make 3 lists: the products, products & size, product & size & supplier.

Products: one column, name the list SelProd (I use named ranges, really helpful here)
Product&Size: Exactly like your unmerged example (4 row, 2 col), name the whole range SelSize
Product&Size: 4 columns: Product, Size, Product&Size (formula: =F5&"-"&G5, basically to glue the product and size into one field), Supplier. In your example that would be 7 row, 4 col. Name it SelSuppl

Next, my data input starts at A17 (for product), B17 for size and C17 for Supplier. Note: my formulas have a ; separator, it might be that yours is a comma.
Validation for A17: =SelProd
Validation for B17: =OFFSET(SelSize;MATCH(A17;OFFSET(SelSize;;;;1);0)-1;1;COUNTIF(SelSize;A17);1)
Validation for C17: =OFFSET(SelSuppl;MATCH(A17&"-"&B17;OFFSET(SelSuppl;;2;;1);0)-1;3;COUNTIF(OFFSET(SelSuppl;;2;;1);A17&"-"&B17);1)

Those cells can be dragged down and should work okay.

Hope that helps,


Watch MrExcel Video

Forum statistics

Latest member
Bigpotato 668

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
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 "".
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