Using UNIQUE & FILTER formula inside the Data Validation toolbox

Question

Hello,

I'm trying to create multiple dependent dropdown list using the UNIQUE and FILTER formula directly inside the Data Validation Toolbox.

The formula (see below) I'm using is working well when outside of the toolbox (tested with F9) and return an array :

The Formula:

=UNIQUE(FILTER(INDIRECT("Table1[Package]"),INDIRECT("Table1[Level0]")=G2,"NOK"))

The returned array with F9:

{"Option A";"Option B"}

But for some reason, the same formula used inside the Toolbox evaluates to an error and the list is not working.

It is important for me to use the Toolbox as I want to repeat the dependent dropdown list in a dedicated form with several lines and each lines will have 4 columns to fill with dependent dropdown

I will really much appreciate any help on this one as I would like to avoid using the OFFSET methods that will be more complicated to implement.

Thanks for your support,

Answer 1

Hi,

No I haven't found a solution. I finally wrote a VBA routine that work as follow:

Manual work:

In a dedicated workbook, I Create a Table object with all possible combination from L1 to L4

VBA:

- the macro populate another table with all unique possible "validation" by reading the first Table

• When clicking a cell within a particular range inside the form , the macro dynamically associate the DV from the 2nd table.

If you need it, I could share my macro or part of it, but I need time to cleanup the confidential stuff in the file

Answer 2

Did you consider using the 'transit station' formula to define a named range, then use the named range for the data validation? I've read examples of that working with the UNIQUE and FILTER functions, but am having trouble implementing it as well.

Answer 3

Hi,

Apologies for the late reply. JTO_SBM. I got my sheet to work with 2 levels, using UNIQUE, TRANSPOSE, FILTER to create 2 transition tables, then OFFSET + MATCH formula in Data Validation. No need for VBA, but thanks for your kind offer :-).

As mentioned, it works only if there are 2 levels. But it may work if one creates several transition tables, one for each level, then reference them with OFFSET, MATCH in Data Validation later. I will investigate that scenario further later.

Answer 4

Hi JTO_SBM,

Could you share your marco for my reference? I'm dealing with an exactly same situation with you. Hope to hearing from you soon.

Thank you!

Answer 5

Hello,

I wish I could upload directly the XLSM to the reply, but it sounds like it is not possible.

Check if you can access the following link: https://drive.google.com/file/d/1TbrrOWUTIsJ5FRDZ2msWIXGHfhcQoox-/view?usp=sharing

Regards,