Apologies, cannot share sheet bc company work.
I have two columns. The first is data validated against a static list, the second is data validated dependent on what was selected in the first column. The approach I am using is to have a reference sheet get indirect() populated based on the first column choice:
indirect("SHEET_NAME!F" & ROW()) //F is the first column and there is a named range that matches the static list of options.
The second column then references to the accompanying
indirect() row through data validation:
Criteria: list from range,
='Relevant Columns xref'!$A2:2
Grabbing the entire column (minus the header row) and setting it to the above works great. However, if I insert a row, it will break all references that follow the inserted row (off by -1).
I am often inserting rows, so it would be nice to not have to manually reset the validation each time.
When I try to have validation triggered on insert (which works) it changes the reference from relative to static:
var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('G2:G1003').activate(); //G is the second column discussed above spreadsheet.getRange('G2:G1003').setDataValidation(SpreadsheetApp.newDataValidation() .setAllowInvalid(false) .requireValueInRange(spreadsheet.getRange('\'Relevant Columns xref\'!A2:2'), true) .build());
This results in validation of
Relevant Columns xref!A$2:$2 (not
Relevant Columns xref!A2:2) for all cells in range.
I don't know how or why the static reference replaces input or if it is possible to prevent it, but ideally I'd be able to refresh the validation when I insert.