Lock data validation list to not move when cells are imported-CodePudding

I've got a set of dates starting in E1 which continues to the right and a data validation list based on those dates/cells, however, whenever I insert a new column before E1 the data validation list will not include the new data at E1 but rather move over 1.

How do I lock this list to E1 as a starting point of my list so whenever I insert columns it stays at E1? Using $ in the formula doesn't work either.

Thanks

CodePudding user response：

You can use a whole row reference, with index, offset to return the required range. Constants in the formula will dictate where the range starts. You can use constants or formula to determine the size of the range

Example for the range E1:J1:

=OFFSET(INDEX(1:1,1,5),0,0,1,6)

=OFFSET(1:1,,5-1,,6)

and after inserting a column at C