Aug 31, 2019
Data Validation doesn't auto increment the range entered in "list from range" when dragging to copy
First:
A1 is Data Validation "List from Range" and pulls the named range "Category"
Then:
T1
=transpose(INDIRECT(A1))fills the row starting at T1 with the cells that make up the named range referenced in A1
Thus creating:
B1 is Data validation "List from Range" and pulls from T1:Z1
Named Ranges
- "Category"
- "Buildings"
- "Improvements"
The issue is I want those two dropdowns to be created down 200+ rows. The first basic dropdown is fine when you drag copy it down rows. The second dropdown's data validation does not increment up when you try to copy it. Thus creating:
- B2 = "List from Range" T1:Z1
- B3 = "List from Range" T1:Z1
- B4 = "List from Range" T1:Z1
When I want:
- B2 = "List from Range" T2:Z2
- B3 = "List from Range" T3:Z3
- B4 = "List from Range" T4:Z4
- ... forever
Is there a way to either simplify this formula/ data validation OR is there a way to increment the data validation field so I don't have to manually change the data validation in 200+ cells?
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Aug 31, 2019
Aug 31, 2019
Hello,
You can't do multiple dynamic dependent drop-downs the way you are trying to do. You will need a script to do this and below is a sample sheet using your "Ledger" sheet...
https://docs.google.com/spreadsheets/d/1Vl28PyKvUGhjNUrk8gjDaBbzOlqA4gZvxqKXqIwpMpU/edit?usp=sharing
...just right-click link and choose "Open link in new tab".
I have "instructions" on the "Ledger" sheet that explains how the script works.
Notice the "data" sheet where in row 1 are your main "categories" and starting in row 3 are the "sub-categories" for each "category". You can add new "categories" to row 1 and their "sub-categories" below as well as add new "sub-categories" to existing "categories" and drop-downs will update.
I learned how to create the script by watching the 2 videos below (be sure to right-click link and "Open link in new tab)...
James :)
PS,
The reason for right-clicking links is cause just clicking the link will close the forum and open the link in same browser tab.
Last edited Aug 31, 2019
2 Videos
Original Poster Matthew Robbins Kirby marked this as an answer
All Replies (10)
Aug 31, 2019
Hello,
You can't do multiple dynamic dependent drop-downs the way you are trying to do. You will need a script to do this and below is a sample sheet using your "Ledger" sheet...
https://docs.google.com/spreadsheets/d/1Vl28PyKvUGhjNUrk8gjDaBbzOlqA4gZvxqKXqIwpMpU/edit?usp=sharing
...just right-click link and choose "Open link in new tab".
I have "instructions" on the "Ledger" sheet that explains how the script works.
Notice the "data" sheet where in row 1 are your main "categories" and starting in row 3 are the "sub-categories" for each "category". You can add new "categories" to row 1 and their "sub-categories" below as well as add new "sub-categories" to existing "categories" and drop-downs will update.
I learned how to create the script by watching the 2 videos below (be sure to right-click link and "Open link in new tab)...
James :)
PS,
The reason for right-clicking links is cause just clicking the link will close the forum and open the link in same browser tab.
Last edited Aug 31, 2019
2 Videos
Original Poster Matthew Robbins Kirby marked this as an answer
Aug 31, 2019
PS,
Apparently you can now just click links and it will open in a new browser tab without right-clicking. They must have updated this recently.
Aug 31, 2019
PS,
With this script there is no need for "Named ranges" as drop-downs in column C are populated with the "Main categories" and the script will "create" the drop-down in column D populated with the "sub-categories" based on choice made in column C.
Aug 31, 2019
Best,
Google user
Dec 17, 2019
Example: The category is in column C, but I wanted to have the items in column E not in D. Is this possible? Thank you very much.
Dec 17, 2019
Bernadeth Guevarra,
Then the offset needs to be changed in both places. Right now the script is set to...
offset(0 1)
...which means to offset the activeCell where the Category drop-down is by 0 rows and 1 column to the right. So it is offsetting column C by 1 column and inserting the other dropdown in column D. Both offsets need to change to this...
offset(0, 2)
...on lines 11 and 21 of the script.
James :)
Google user
Dec 17, 2019