If you need multiple validation rules for multiple cells, you should also have multiple ranges for them, so that the rules are tracked independently of each other.
But the second issue, with an illegal choice staying in F after an edit to D, can't be solved with formulas alone. So I wrote a script that keeps better track of validation in column F. Note that it does not set the initial validation rules (before first use); that you can do manually. It also doesn't set rules for column D, which are static.
What it does is the following: when a cell in column D is edited (header row excluded), it adjusts the validation rule of the cell two steps to the right accordingly. Additionally, if that cell contained a value such as OptionB that is no longer allowed when D is blank, the value in F is removed.
In the script, e.range
is the cell edited, e.value
is its new value.
function onEdit(e) {
if (e.range.getColumn() === 4 && e.range.getRow() > 1 ) { // only Column D, not header
var optionA = SpreadsheetApp.newDataValidation()
.requireValueInList(['OptionA'], true).build();
var optionsABC = SpreadsheetApp.newDataValidation()
.requireValueInList(['OptionA', 'OptionB', 'OptionC'], true).build();
var affectedCell = e.range.offset(0,2,1,1); // two steps to the right
if (e.value === '') {
if (affectedCell.getValue() !== 'OptionA') {
affectedCell.setValue(''); // reset if current choice is no longer legal
}
affectedCell.setDataValidation(optionA);
}
else {
affectedCell.setDataValidation(optionsABC);
}
}
}
This is not really specific to sheet duplication: range references in data validation rules are always treated as absolute by Google Sheets, nothing is relative to current cell position (try copying a cell with data validation to see this).
So, I wrote a script that localizes all validation-by-range rules in the current sheet, making the ranges refer to the same sheet. It is executed with a custom menu item "Localize Validation". So the whole thing is a two step process:
- Duplicate a sheet
- Run "Localize Validation" on the duplicate.
The script:
function localizeValidation() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
var rules = range.getDataValidations();
for (var i = 0; i < rules.length; i++) {
for (var j = 0; j < rules[0].length; j++) {
if (rules[i][j] && rules[i][j].getCriteriaType() == SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
var cValues = rules[i][j].getCriteriaValues();
cValues[0] = sheet.getRange(cValues[0].getA1Notation());
rules[i][j] = rules[i][j].copy().withCriteria(SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE, cValues).build();
}
}
}
range.setDataValidations(rules);
}
function onOpen() {
SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", [{name: "Localize Validation", functionName: "localizeValidation"}]);
}
Best Answer
yes that is possible. check this example:
Sheet1:
D20: Data Validation > List of items:
I made a choice,I didn't make a choice
F20: Data Validation > List from a range:
Sheet2!D8:D11
Sheet2:
D8:
=IF(Sheet1!D20 = B6; QUERY(B8:B11;;-1);IF(Sheet1!D20 = C6; QUERY(C8:C11;;-1);))