I have an issue when adding a line into a sheet where it kicks the drop down dependencies out of order.
Column E is a list of option dependent on the input of column D. All works fine, referencing a master data sheet.
However, when I insert a row into the "Team Activity Recording" tab, it copies the data validation from the above row and throws all subsequent rows out of alignment for the data validation.
EG Row 8 references Row 8 on the data drop down sheet, row 9 references row 9 and so on. If I insert a row above 9, both the new row 9 and row 10 reference row 9 on the data sheet.
I would like to know how I can add and remove rows in the "Team Activity Recording" tab and maintain the correct data validation?

Test Sheet


1 Answer 1


Each time you insert or remove a row, you will need to reapply the Data Validation for your dependent lists.

  1. Copy a working range, e.g. D7:E7
  2. Select the entire range, e.g. D7:E21
  3. Paste Validation Only Alt+Shift+E then s then d

The alternative is to use scripting and a trigger to do this for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.