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”)
)

​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”))  Read More

​