Multiple cascading dropdown lists in Google Sheets Solved

Question

Hello,

I'm trying to build a table with dropdown lists under certain conditions.

I have 7 product families, and within each family, a certain number of pieces of equipment.

In my table, there is a column with a dropdown list of these families.
In the adjacent column, I need to retrieve only the equipment from the selected family in the previous column.

I managed to do this for one line using the "Offset" formula, which generates a list based on my family in row 1. I then retrieve this dropdown list in my table.

However, I need to do this exercise for each row, which can create a very large number of lists, especially since I want to make a table for each month. This wouldn't be feasible.

Do you have a simpler solution?

Thank you.

PapyLuc51 · Accepted Answer

I think I have found the formula that I had made and that inspired me this video

I only include images that I think will be quite explicit
1 the list sheet

2 the sheet of cascading drop-down lists incremented downward

PapyLuc51 · Answer

Hello I deleted my previous post because in Google Sheets you should not use Offset See this >> Video << and this search Best regards

Shinshu · Answer

Hello and thank you for this response,

I used this tutorial to create my table that uses the OFFSET formula.
It allows me to make a list in one tab and I use data validation to retrieve this list.

However, I quickly find myself stuck because I have to repeat this operation for each new row. So if my table has 100 rows, I need to create 100 lists and 100 filters.

Example:
Column A Column B
Fruits Apple
Fruits Apricot
Meats Chicken
Vegetables Spinach

PapyLuc51 · Answer

Good evening,

I don't quite understand what you want!

Once the dropdown list is set in the first cell, hold down the click on the fill handle (the bottom right corner symbolized by a square) and drag it down for the desired number of rows.

Otherwise, select all the cells in the column to be processed before setting up the dropdown list.

If it's something else, please explain your problem a bit better.

Best regards

Shinshu · Answer

It will be easier with an image.

I have a list of equipment for each family.
I am required to create a filter for each new line

In this photo, you can see my filters 1 and 2 for my 2 lines. Is there a simpler and faster way? Knowing that I could have 200 lines and I would like to create a tab for each month?

PapyLuc51 · Answer

Hello,

Well, I'm giving up; sorry, I don't know enough about the specific intricacies of Sheets to be able to respond correctly to the request. However, I'm looking, and if I find something, I'll let you know.

I'm letting the specialists take over. I recommend attaching a copy of your Sheets file by clicking on the link creation icon and pasting it into the response.

Good luck
Best regards

PapyLuc51 · Answer

Hello,

I'm back

I found an indirect way; it's worth what it's worth (until a better one comes along) - the explanations are on the "LD" sheet

https://docs.google.com/spreadsheets/d/1Cevy5Qkl_gu19XysHKq6v2cCb7VOBgmeAO7DcHurwGQ/edit?usp=sharing

And always hoping I understood what you're asking.

Best regards

Shinshu · Answer

Hello,

I will check this link and test it.
Otherwise, I have provided access to my spreadsheet to make it easier.

https://drive.google.com/file/d/14CvAN3LHIKxZx2_Cf2GcTT2PipemmarS/view?usp=sharing

Thank you for your help and research.

Gerva · Answer

Hello,  I have the same issue as Shinshu and I’m interested in PapyLuc's solution, however the link is no longer available.  Thank you for the help.

Gerva · Answer

Great solution, thanks!
I'm hiding the columns from C to AK to make it look cleaner, and there you go!

Here's a follow-up question to improve the file's architecture:

Let's say I want to put all my transposed rows in a new sheet called "DO NOT TOUCH". The goal is to separate the listing sheet that the user will fill out from the sheet corresponding to the "program".

The following problem arises when sorting the listing (alphabetically or otherwise): The transposed rows in the "DO NOT TOUCH" sheet are sorted correctly, but the validation ranges in B2:B retain their original row values. That is to say, for example, if my row 3 has become row 6 after sorting, then my new cell B6 contains the value range (C3:AK3). It should keep the value (C6:AK6). Therefore, the rows of the two sheets no longer correspond.

Is there a trick to remedy this? For instance, by modifying the validation range of B2:B to make it dynamic? Cell B2 keeps C2:AK2, cell B3 keeps C3:AK3 ...

I hope I made myself clear and I'm not being too demanding :)

In any case, thank you very much, Grandpa!

PapyLuc51 · Answer

I noticed that if you only sort column A of the listing, column B does not follow.

So you need to select both columns containing the two drop-down lists in the listing sheet and go to Data / Sort the range by column A.

Best regards

Multiple cascading dropdown lists in Google Sheets

11 réponses

Texture issues in enshrouded

Big fish games are they worth it?

Saracroche: the effective french app against phone spam

How to get the character µ on the android keyboard.

Access issue to my "contact me" profile

Windows spotlight is not working.

Black screen digital camera

Connecting rj45 cable with fiber

Sony bravia tv red led blinks 4 times

Free: associating with the freebox server in progress