DYNAMIC DEPENDENT DROP DOWN LISTS IN GOOGLE SPREADSHEETS
Why Stack Overflow recommends it
Stack Overflow users link it when answering questions about dependent or conditional drop-down lists in Google Sheets, since the video's script is a ready-made solution.
Recommended by 5 people across 5 different months, 2016 to 2020.
What it is
A screen-capture tutorial by Jason Jurotich showing a short Google Apps Script that builds dynamic, dependent drop-down lists in Google Sheets using named ranges. It is presented as a faster, simpler alternative to his earlier videos, with a script link in the description.
What people said
Recommends user4935235 · 2016-02 · Stack Overflow · on “Drop-down dependent menus in Google Spreadsheets”
I answered this question about six months ago on YouTube ( [this video] ). The classical way to do it, where in column A you have the main drop down list, and in column B you have the second drop down list which is dependent on the first in column A, is below. I have also added script to be able to have a second dependent drop down list in column C dependent on column B. This script requires that you have named ranges already set up within the spreadsheet, otherwise it will not work. function depDrop_(range, sourceRange){ var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build(); range.setDataValidation(rule); } function onEdit (){ var aCell = SpreadsheetApp.get
Recommends Mreider · 2018-03 · Stack Overflow · on “Conditionally lock / unlock cells in google sheets”
I think I found the answer via some extra Google searching. This is a YouTube video on how it works, followed by a link to the code in the video: [this video] [LINK] What the author does is create some ranges in a different sheet as lookup values, and then populate the select box with the values from the matching column. function depDrop_(range, sourceRange){ var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build(); range.setDataValidation(rule); } function onEdit (){ var aCell = SpreadsheetApp.getActiveSheet().getActiveCell(); var aColumn = aCell.getColumn(); if (aColumn == 1 && SpreadsheetApp.getActiveSheet()){ var range = SpreadsheetApp.getActiveSheet().getR
Recommends Tedinoz · 2018-10 · Stack Overflow · on “How to create a dynamic drop down list using a cell with comma separated values in google sheets via apps script”
I've seen the [this video] on which this code is based. It is a very, very simple solution to a very, very, complex problem. My strong suggestion is that you to try and utilize the script, but to use your comma separated values as the input ranges. I suggest extracting the csv values from the respective cells using this equation. =TRANSPOSE(SPLIT(B3,",")) Use SPLIT to separate the individual elements in the cell. Then use TRANSPOSE to orient the data vertically. You could also throw in SORT, if the data was not in the "right" order. [LINK] The screenshot shows the values in a csv format; they are extracted and transposed using this formula. And then the range is named, as the YouTube video requir
Recommends Leanna · 2019-12 · Stack Overflow · on “Return cell value by criteria in same row AND row above”
This is a very complicated question. I'm not even sure if what I'm hoping for is possible, but I have been surprised many times before. Here we go.... Here are the links to the two spreadsheets I am modeling my situation after: John Doe [LINK] and Jane Doe [LINK] . In these spreadsheets, I recently set up a script for making dynamic dropdown lists according to this video: DYNAMIC DEPENDENT DROP DOWN LISTS IN GOOGLE SPREADSHEETS [this video] . It works great for me, but there is a whole level on top of it that I need to establish. First and foremost, be aware that I cannot move any of the cells/rows/columns. In my system here, I have two individuals working on a TASK (TASK1), one a Writer and the
Recommends marikamitsos · 2020-02 · Stack Overflow · on “Dependent Dropdown styled, filtered sum advanced annual budget tracker”
I quite often use this script by Jason Jurotich . You can find it in the description on his YouTube channel: DYNAMIC DEPENDENT DROP DOWN LISTS IN GOOGLE SPREADSHEETS [this video] Please have a look at a copy of your sheet I prepared and is using the above mentioned script. [LINK] This is an exact copy of the above mentioned script: function depDrop_(range, sourceRange) { var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build(); range.setDataValidation(rule); } function onEdit() { var aCell = SpreadsheetApp.getActiveSheet().getActiveCell(); var aColumn = aCell.getColumn(); if (aColumn == 1 && SpreadsheetApp.getActiveSheet()) { var range = SpreadsheetApp.getActiv
Cites user2240778 · 2017-04 · Stack Overflow · on “Conditional Data Validation google sheets”
I have 3 columns, all of which will have dropdowns (data validations). Col 2 and 3 will show only if Col 1 is set to "Yes" Col 1 | Col 2 | Col 3 Yes No Is there a way of using the if statement to load a dropdown or a google script I have tried using this article/video [this video] It works only if the Named ranges are linked, if not, it doesnt work. In this case Col 2 and Col 3 are unconnected only thing common is the "Yes" in Col 1.