Google Sheets/Excel get named ranged name based on cell value with multiple rows for dependent dropdown

0
votes

I would like to use a dependent dropdown with a named range name based on the cell value on the first column of each row or beside it. This column is a dependent dropdown as well with a defined name range somewhere in the sheet.

Something like these tutorials but they only apply on one row.

https://productivityspot.com/dependent-drop-list-google-sheets/#comment-1609

https://www.excel-easy.com/examples/dependent-drop-down-lists.html

https://www.excel-easy.com/examples/excel-files/dependent-drop-down-lists.xlsx

It would be nice if it would work on both excel and google sheets.

Screenshot example with Column C is the first dropdown and D will get the named range value.

I found this formula below which generates the list on another area in the sheet, but only applies on the first row A2..

=ARRAYFORMULA(INDIRECT(A2))

should be applicable with A2,A3 and so on.

1
excelvalidationgoogle-sheetsdropdown
I am not very sure what is your problem it would be good if you share sample sheet & what you want to achieve, with the formula you need to specify array =ARRAYFORMULA(INDIRECT(A2:A)) - Shiva
Thanks @Shiva, the second tutorial link there has link to the downloadable example sheet. Edit: I have also added the resource sheet on my question - smurfee
Sorry i dont understand your problem the sheet you have provided does the job & its from tutorial, specify what is the issue you are facing show, what you have tried so far & what you want to achieve - Shiva
The tutorial only works only for 1 row and as I mentioned in my question I am looking for a solution that works on multiple rows. The INDIRECT command is what I have tried so far, but no idea on how it will work or what formula for multiple rows. - smurfee

1 Answers

0
votes

What you are trying to achieve can be done in 2 ways it can be done

1) Via App Scripts WATCH THIS

2) Via Google sheets formula READ THIS