Forum Discussion
FORMULA Ranges keep changing even if I used a Named Range
I have been having issues with ranges changing even when they are fixed $A$7:$A$1600. So I created named ranges in the Name Manager. However, I am seeing those values change after releasing it to the business users.
I ultimately want to know how can I force these ranges to stay as they were designed?
The affected cell have #N/A in them because some of the ranges in the LET statement are different from others. Once they are re-aligned, then all is good.
Some examples:
These are how I set them up originally (exception is the fb_FamilyName where the it has a crazy value which was originally a PI_PackageEditable!$BK7. Not sure how that value go to be as big as it is. Given it is not fully locked down, I can see that it could change. The others however should not change but they do.
The way they were originally set up.
Added 5 rows and things changed.
And here is another that changed even though it has nothing to do with the worksheet I am working on
Here is an example of a formula making use of the named ranges. if one of the ranges moves out of synch with the others, then the cell shows a #N/A.
=LET(
centreBalloon, FILTER(pipkg_LongDesc_Rng, (pipkg_RecordType_Rng="COMPONENT")*(pipkg_PCODE_Rng=$D7), ""),
firstComponentRow, MATCH(1, (INDEX(centreBalloon,,1)="COMPONENT")*(INDEX(centreBalloon,,8)<>"COUPON_ADDON"), 0),
centreBalloonDesc, INDEX(centreBalloon, firstComponentRow, 11),
primeText, PROPER(centreBalloonDesc)&" Bouquet with "&"[COLOUR] [SIZE] [TYPE] Balloons - ",
pieceCount, SUMPRODUCT((INDEX(centreBalloon,,1)="COMPONENT")*(INDEX(centreBalloon,,8)<>"COUPON_ADDON")*(INDEX(centreBalloon,,7))),
CONCAT(primeText, pieceCount, "pc")
)
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.
HansVogelaar thanks Hans. That is indeed what I did and it seems to work well.
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.
