Can't get Data Validation to change when inserting a new row

Question

I'm working on a spreadsheet that relies extensively on data validation to update my dropdown menus for selection. I've managed to get it all working pretty well, but the issue I'm having now is when I attempt to insert a row in the middle of a sheet. For example, when selecting a system in row 6, my data validation formula is ='COMPONENT DATA'!$B6# . It allows me to select anything within the results in row 6 on my data sheet.

When I need to make a new entry or an edit in the spreadsheet, I need to add a row in the middle. If I were to add a row above row 6, the data validation in that cell would reference B6 in my data sheet, but the validation in row 7 also references row 6, instead of shifting down to now refernce row 7, and onwards. What can I do to fix this so that the formulas shift down when I add a new row?

Answer 1

Hi Lolotte5511,

Greetings! Thank you for posting to Microsoft Community.

From your description, it seems to be Excel design limitation.

As workaround, you could use "format painter" to correct them.

Best Regards,

Snow Lu