Offset Reference Cell

[Alex Mackenzie]

I am using the OFFSET function with a spreadsheet created by our IT
department. I have added the offset function to help sort the results in the
spreadsheet. When initially entered the function works fine. However, when
the data is updated (background macro, code is protected), the "refer to" in
the named range changes to OFFSET (sheetname!#REF!). Before I go to my IT
folks, I would like to understand what could be causing the reference to
change. Any ideas would be appreciated. Thank you.

 

[Luke M]

It could be the background macro is actually deleting the cell at one point,
and then filling it in later with something else. Other option is if it
somehow inserts rows/cells that push your reference cell off the sheet.

When it deleted the cell, your formula lost the REF, and created the error.

You could use INDIRECT though to get around this. E.g.,
=OFFSET(INDIRECT("'Sheet2'!C6"),1,1)

This will always look at Sheet2, C6, no matter if the current C6 gets moved
or deleted.