Multiple dependent drop down lists to the same database

[pilgrimen]

Hi,
New user here with a question that I haven't seen the answer to. Not native English speaker and not that experienced with excel so I might be using the wrong words or stuff like that.

is a video that goes into the question a bit but the tricky part is that I want to have as many possible entries as possible for the user.

Here's a picture of the database I've created (in Swedish) and some colorful rectangles to try and show the desired funcionality:

I want it to be possible for the user in the first drop down list to choose "Mark", in the next one "Lekytor", in the next one "Lekutrustning" and so on.
What I've tried this far is the method in that youtube video with B2=UNIQUE(FILTER(Huvudgrupp;Huvudgrupp<>"")), C2=UNIQUE(FILTER(Byggdel;Huvudgrupp=B2)), D2=UNIQUE(FILTER(Åtgärdsgrupp;Byggdel=C2)) and so on (Where these formulas are in a different sheet than the database).
This method doesn't seem to be possible without the (from the above linked youtube video):

I tried to paste the whole formula =UNIQUE(FILTER(etc)) into the data verification list but excel can't manage that.
As I potentially have 100 or so spill lists to take in consideration if all the rows should have their own "drop down prep" areas, quite much work seems to be needed.

Here's a picture of how I want the user interface to look like.
All of the headers and info refers to a book that we can put in information from when needed so my plan is to make the database open so it can grow when people need to put in more stuff.

Can I do all of this without VBA or is it needed?
Have I been at all coherent with what my question is or is it something that needs to be explained further?

Many thanks in advance for any help at all.

 

[Akuini]

@pilgrimen
See if this helps:

3 or More Dependent Data Validation, with VBA, easy to set up

I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might...

www.mrexcel.com

 

[pilgrimen]

@pilgrimen
See if this helps:

3 or More Dependent Data Validation, with VBA, easy to set up

I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might...

www.mrexcel.com

Thank you! Will take a look, didn't find that one when I searched but not that easy to find everything...

 

[pilgrimen]

@pilgrimen
See if this helps:

3 or More Dependent Data Validation, with VBA, easy to set up

I want to share a macro to set up multi dependent data validation with vba. Some versions of dependent data validation use formulas to set them up, usually with indirect functions. For 2 or 3 dependent data validation & small data set, it's pretty easy to set it up, but for more than 3, it might...

www.mrexcel.com

I implemented it and it works! YAAY!
But, I noticed that excel takes a while to think (like 5-10 seconds) every time I've chosen something in the list.
My table is something like 200 rows as of now and in the future it will probably grow to 800 or something.
Can that have anything to do with it running slow or have I done anything weird when I applied your code?

I'd like to have the possibility to have the first column blank, is that doable?

Here's a link to the file.

Bok2

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[Akuini]

Can that have anything to do with it running slow or have I done anything weird when I applied your code?

You set the table up to last row of the sheet (i.e row 1048576)!!!. That makes the code significantly slow. Just set the last row to about 10 rows below the last row with data, and when you need to add new data then just drag down the small blue arrow at the bottom right corner of the table.

I'd like to have the possibility to have the first column blank, is that doable?

What do you mean? Can you elaborate?

 

[pilgrimen]

You set the table up to last row of the sheet (i.e row 1048576)!!!. That makes the code significantly slow. Just set the last row to about 10 rows below the last row with data, and when you need to add new data then just drag down the small blue arrow at the bottom right corner of the table.


What do you mean? Can you elaborate?

Yeah, that was my guess too.
I changed here:

but then I got an error here:

It seems like I'm defining the range in some kind of faulty way..
How should I define the range?
In your example the ranges was defined as A:A, B:B and so on (or other letters) so I just did like had done.

I see that I wasn't very clear in my question.
I'd like the first data verification list (the one to the left) to be blank until a choice is made. I can just add an underscore in one of the rows in the DATA-sheet or is there a better way?

Again, thanks again for your help!

 

[Akuini]

You set the table up to last row of the sheet (i.e row 1048576)!!!. That makes the code significantly slow. Just set the last row to about 10 rows below the last row with data, when you need to add new data then just drag down the small blue arrow at the bottom right corner of the table.

By setting up the table, I didn't mean to set it up in the code but in the sheet (i.e sheet data). Here's what I meant by "Just set the last row to about 10 rows below the last row with data,":

you can see I set the blue arrow at the bottom right of the table is at row 218, it means that the last row of the table is row 218, whilst in your workbook the blue arrow is at row 1048576, it means that the last row of the table is row 1048576.
To change the last row of the table, just click on the blue arrow and drag up or down.

I'd like the first data verification list (the one to the left) to be blank until a choice is made.

When you first create the data-validation, it should be empty, then copy down as needed. But if there's already a value then just delete them, the data validation will be intact. Deleting a cell value will not remove the data validation in it.