Is there a way to generate dynamic excel data validation lists based on source table?

Question

I have two workbooks, one is the source of validation data (Source workbook) and second is for filling in values (Order workbook).

In the source workbook Sheet1 I have a table with data like this (there can be an expanded amount of paper and grams combinations)

In order workbook (where actual order if filled in) I use references to source workbook using data validations combined with named tables.

In source workbook I need Grams data validation lists based on the Paper entered for each of the rows, here is an example with NovaPress Gloss we see only three selections:

with ArcticSilk+ we see 5:

These drop lists were hard coded to show what I need to fulfil.

To sum up I need dynamic data validation lists based on other cell value which is the same value as in the source workbook table for each of the entered orders (I, II, II etc.).

All I managed to find was this rather nice answer Advanced Dynamic Validation Lists in Excel but it used intermediate table for "dynamic" list and in my case I need to have many independent lists.

Can this be achieved using just excel functionality or some VBA code is needed? All help is appreciated

Answer 1

Assuming within the same workbook, you have the following two structured reference tables:

1. GSM - This is the table that holds the GSM data for each paper product in the following structure:

2. Order - This is the table that you select the GSM from a dynamic data validation list for each paper product:

Firstly, you need to set up a named range called List_GSM using the following formula:

=XLOOKUP(Order[@Product],GSM[#Headers],GSM)

Please note, if your version of Excel cannot use XLOOKUP, you can use a combination of INDEX + MATCH to fetch the range of GSM for each product in a similar way.

=INDEX(GSM,,MATCH(Order[@Product],GSM[#Headers],0))

Secondly, set up the drop-down list using the named range List_GSM in the GSM column of the Order table:

You can set it up in the first empty cell and then drag it down to apply to all cells in that column.

The drop-down list will look up the product name in the first column and return the corresponding GSM list from the GSM table.

The key to this methodology is to make sure the Order table's product names are the same as the headers of the GSM table. You can either use power query to pivot your source table or manually set up the GSM table so that the product names are in the header.

One obvious advantage of using structured reference table is that you can update your source table with new product data. The drop-down list in the Order table will be updated dynamically. See the below example:

Let me know if you have any questions :)