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 JTO_SBM,

Thanks for sharing your experience with us.

From your description, I understand you want to create multiple dependent dropdown list by using the UNIQUE and FILTER formula with the Data Validation Toolbox.

When I test from my side, I find that we cannot directly use the formula inside Data Validation Toolbox because it cannot find the unique value from the table.

As workaround, you may create a "transit station" which is used to firstly get unique value from the table, and then create data validation based on the value from the "transit station".

You can find the example file from this shared link: https://1drv.ms/x/s!AqyUzW7T2xzFlheZDi_pVaNzuTS-?e=JKScX0

Merry Christmas!

Best Regards,

Tina

Answer 2

Hi JTO_SBM,

Welcome to share any update with us when you have time.

Thanks,

Tina

Answer 3

Hello Tina,

Many thanks for your reply or should I say "Christmas Gift" considering the reply date :-)

Let me first start by Wishing you a Happy and healthy New Year for 2021. Hope the world will soon goes out of this pandemic.

I just investigated your proposition, but unfortunately, It won't fit my need.

Actually, as you can see on the attached print-screen, I wish to populate a form (green table) from L0 to L3 using dependent data validation, for each line of the form.

If I use a transit station, I will need one transit station per line. It would be OK if I would have only few lines in my report, but I will have up to 500 lines in mine.

So far, I've used a VBA macro to populate the correct "validation content" depending on the precedent cell at the selected line but I would rather prefer to rely on formula so I'm trying at the moment to build it using INDEX and MATCH function. Unfortunately I experience difficulties with filtering aspect (I do not retrieve unique result in my validation list). Also I expect formulas will become very long and complex when I will extend to L2 and L3.

I'll keep you posted if I find a way of making it work with formula.

Thanks for your support,

JTO

Answer 4

Hi JTO,

Thank you for posting back and sharing the detailed information.

Per your description about the scenario, VBA method is a good workaround to meet your requirement, I'm afraid formula method will be more complex.

You are welcome to post back when you have any update with formula method. We will also try to see if there is any way to meet your requirement with formula.

At the same time, community members who have the similar experience are welcome to share insights and suggestions here.

Best Regards,

Tina

Answer 5

Hi JTO,

I am facing a similar challenge. Have you found a solution with formulas?

I can cut down from 4 to 2 levels (concatenating L1-L2 and L3-L4 into a transition table) for sake of simplicity but still haven't found a working formula for the DV toolbox.