Forum Discussion
FORMULA Ranges keep changing even if I used a Named Range
If you have a named range that refers to =PI_Package!$A$7:$A$1605, that range will shift if the user inserts or deletes cells above A7. To ensure that the named range will always refer to A7:A1605, make it refer to =INDIRECT("PI_Package!A7:A1605")
Since INDIRECT takes a string argument, it it not affected by cells being inserted or deleted.
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 ended up deleting the named range and manually recreated it via the Name Manager. The prior method of creation was manually selecting the Cells and using the Name box to the left of the Function bar to set the Range's Name.
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.
