I have two sheets, both have a column that use data validation in Column G/#7, starting from row #8 and all the way down. I have to edit the data validation every once in a while and match the lists for both sheets. This can be annoying since it starts from row 8 down to row 1000+ and needs to be done for both sheets.
How do you make it so that you have a third sheet called “settings” where there will be a master list where in one column there will be a list of rows and for each row it takes the data and automatically updates the data validation list for both sheet one and sheet two?
e.g.
1 | Master List (Title) 2 | John Doe 3 | Jane Doe 4 | Steve Smith 5 | Stacy Smith
and it makes the data validation for column 7, row 8+ (all the way down) for BOTH Sheet 1 and Sheet 2 as such:
John Doe,Jane Doe,Steve Smith,Stacy Smith
And if a name is added, it adds it to the data validation list / updates the list. If a name is removed, it removes it from the data validation list.
— Photo examples provided:
We have a column that uses data validation to list out items.
We also have a “master list” with all those items. If I update that “master list” I want the data validation to be updated so I don’t have to go into the settings for data validation but only update my list since it is always changing so I can have an updated dropdown for that column.
Advertisement
Answer
I believe your goal as follows.
- You want to update the datavalidation rules at the column “D” (the range is “D2:D”) on the sheet of “Members”, when “Master” sheet is updated.
- You want to achieve this using Google Apps Script.
In this case, I would like to propose to run the script using OnEdit trigger.
Sample script:
Before you use this script, please set the sheet names of “Master” and “Members” sheets. From your question, I couldn’t understand about the correct sheet names. When you want to run the script, please update the cells of “Master” sheet. By this, the datavalidation rules at the cells “D2:D” on “Members” sheet are updated.
function onEdit(e) {
const masterlistSheetName = "Master"; // Please set the sheetname.
const membersSheetName = "Members"; // Please set the sheetname.
const ss = e.source;
const master = ss.getActiveSheet();
if (master.getSheetName() != masterlistSheetName) return;
const range = master.getRange("A2:A" + master.getLastRow());
const members = ss.getSheetByName(membersSheetName);
const dataValidation = members.getRange("D2").getDataValidation().copy().requireValueInRange(range).build();
const length = members.getRange("D2:D").getDataValidations().filter(String).length;
members.getRange("D2:D" + (length + 1)).setDataValidation(dataValidation);
}
Note:
- This sample script is run by the OnEdit trigger. So when you directly run the function
onEditat the script editor, an error occurs because the event object is not used. Please be careful this. - This sample script supposes that your values for datavalidation rules are put to the cells “A2:A” in “Master” sheet. When you want to change this, please modify the above script.
- This sample script supposes that your datavalidation rules are put to the cells “D2:D” without the empty rows. When you want to change this, please modify the above script.