0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.