Data Validation Source Changing when inserting a new row.

Question

Hi All,

I am having an issue that I can't seem to find any information on.

I have a table that includes a column with drop down list selectable in each cell using data validation. This drop down list is based on a named range from another sheet. To populate that named ranged, I will make a selection based on a specific cell.

My problem is that the data validation source is changing within the column no matter what I do.

For example the first row in my column is correct. "N7" is the source for the indirect data.

=INDIRECT(SUBSTITUTE(N7, " ", "_"))

However when I insert new rows, or try and apply the data validation source to the whole column, it will add to code

=INDIRECT(SUBSTITUTE(N8, " ", "_"))

=INDIRECT(SUBSTITUTE(N9," ", "_"))

=INDIRECT(SUBSTITUTE(N10, " ", "_")) etc:

There is no data in N8,9,10 to reference so my formula doesn't work...

Answer 1

Does it help if you use

=INDIRECT(SUBSTITUTE(N$7, " ", "_"))

Answer 2

YES!

Thank you! For whatever reason I didn't think about using the absolute...