Excel Named Range resolving to its actual target

Question

This is a weird problem I'm seeing in Excel

1. Say I have a named range MY_RANGE which points to cell $A$1 (or A1)
2. If I then enter =MY_RANGE into cell A2 all looks good
3. If I then try to edit cell A2 I see =$A$1 rather than =MY_RANGE
4. If I enter =MY_RANGE on a seperate sheet in the same workbook everything works as expected.

I'm guessing there may be some property on the sheet that controls this behaviour but I can't find it. The problem I'm having is that when I copy & paste the cell it copies the resolved reference rather than the named range

Answer 1

I've kind of figured out a solution... I've written a macro to do the following

1. Copy the formula (as a string, so "=MY_RANGE") into a different sheet at the same relative location (so cell 3,3 on SheetA to cell 3,3 on SheetB)
2. Copy the cell on SheetB back to the Cell on SheetA

I still have absolutely no idea what is causing this odd problem but this is a reasonable workaround for me at the moment