Dependent lists based on multiple not unique table columns

Hi Guys,

i have table like here:

Screenshot_49.png

and i wnat to do dependent unique lists (drop downs) from left to right in seperate table for user input.

1. So in Division column i shouldhave just unique list based on division
2. If user will choose "Xbox" he will have in Drop down in column App = "Baden" and "Marko" (no duplicates!) as result to choose
3. If user will choose "Baden" - he will get only "4" in drop down in function column, if he will choose "Marko" he will get only "5" in function column in drop down lists.

Can anybody help how to build ?
Maybe pivot table?

Hmm please help,
Jacek

hi, i looked up on the forum and didnt find.
thank you for getting involved.

Also,

i know the website provided. And i looked also for other solutions. There is no what i am looking for - exactly in details i described what the goal is and attached example.
There is a way to have seperate lists which are refering one to other. But there we do not have duplicates. And there are in seperated lists not i one table with specific column names.

My case is different.
I want first of all get rid of duplicates from table in drop downs.
Second i want to restrict each column from left to right and have only uniques in my drop downs.

As in example for "Xbox".

Please help,
Jacek

o wow thank you very muhc bebo021999!!!

God bless you!
Jacek

Ok i will reopen topic because when i have 10 columns this solution seems to be very hard to implement

For each column i have to create sepearate list plus add multiple formulas.

Maybe somebody knows any different ideas?

Jacek

Hi,

anyone has another idea or it is rather not possible?

Jacek

Ok Guys!

i did the workaround for it but hope that someone smarter than me will figure out something easier to implmenent.

I added pivot tables as many as column number for drop downs - so in this case 3.

And with VBA i am creating key for each pivot which is concatenation of each row from pt.
And next i am using formulas something similar like here:

Please Login or Register  to view this content.

So pivot table ensures that i do not have any duplicates plus key ensures that i can have different configurations for lookup string.
Solution is fast because VBA is run only one time to get keys and based on those keys i have named ranges which i can use in drop downs.

Anyone has easier solution?

Best,
Jacek

Hi, jaryszek

This thread is several months old but you asked for a different solution, so:
Here's an example of multiple dependent data validation using VBA.
This method will make it easier to maintain the data set because you only need 1 table.

Note:
1. You can set up 2 or 3 or more dependent data validation.
Change this part to suit:
'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART:

2. One caveat of using macro is when a macro changes/writes something in the sheet it will clear the Undo Stack, so at that point you can't use UNDO.
In this case it happens whenever you put the cursor in the cell with the data validation.


The example:

Thank you!

I will take a look,

Jacek