Forum Discussion
FORMULA Ranges keep changing even if I used a Named Range
This was also helpful for me!
I'm having almost the exact same issue, however, I was able to identify the triggering action that caused the 'RefersTo' range to change and although this was a helpful workaround, it doesn't answer the question as to why this is changing in this way.
So in my case, I default a name to refer to range("$A$2:$A$5000"). No rows are inserted after this is assigned. When I change a blank cell in this range to any other value, the named range 'RefersTo' shifts to starting 1 row after the row of the cell that was changed and ending an equal number of rows in addition to the original ending row. Example, I alter cell A5, the named range auto-updates to refer to ("$A$6:$A$5004") a shift of 4 rows from the original.
There are no triggering macro events on any sheet in the workbook. This also only happens on Sheet1. I have several sheets with the same setup, but those named ranges are not impacted by the same action.
Why does this happen? Although your solution is temporarily helpful, I hesitate to use INDIRECT on HUNDREDS of named ranges as if they are shifted due to updates, then all Named Ranges using INDIRECT would need to be manually updated to correct their references.
I'm not certain, but think that since there are some merged cells (merged across rows only, not columns) in the affected range, that it may have had an impact on the Named Range properties and contributed to the issue. Since manually creating via the Name Manager, the same action no longer causes the 'RefersTo' range to change.