Named Range Changes when cells are shifted up even though it is locked

by Tom Smith   Last Updated January 11, 2019 12:01 PM

I have a named range of
=OFFSET('Student Information'!$B$8,0,0,Lists!$B$3,1).

When the row is shifted up in a later routine this range will change to
=OFFSET('Student Information'!#REF!,0,0,Lists!$B$3,1).

I need it to stay as the original. I have no idea why this is working like this.

Answers 1

I've tested your issue and for me it happens only if I delete 'Student Information'!$B$8, and that's the expected behaviour.

If I delete column A or rows 1:7, then reference changes accordingly (e.g. after deleting column A it becomes 'Student Information'!$A$8).

If you delete the referenced cell Excel doesn't know where do you want the new reference to be (if you want it at all), so it gives you error.

As a workaround you can use INDIRECT:
=OFFSET(INDIRECT('Student Information'!$B$8,TRUE),0,0,Lists!$B$3,1)
This will always refer to B8 regardless of the changes.

Máté Juhász
Máté Juhász
January 11, 2019 11:51 AM

Related Questions

Excel 2016 - Any suggestions on functions?

Updated November 10, 2017 00:01 AM