My Named Range reference keeps changing depending on what cell my cursor is in (!?)

[d0rian]

What kind of sorcery is this? I was doing some basic cleaning up of a large (30MB) file and noticed something inexplicable. I opened the Name Manager window and saw something that didn't look right: my "cad_sortcol_CP" Named Range apparently referred to cells $Y266:$Y50264:

That didn't seem right at all (it usually referenced rows 2 to like 50,000 or something around there. Then I discovered something even weirder. Without changing anything at all in the file, I just put my cursor in a different cell (in row 390), and re-opened the Name Manager window and saw this:

WTF? Tried once more, putting cursor in a cell in row 2820, and re-opened Name Manager:

What on earth is going on here? Why would a named range definition possibly change merely because I put my cursor in a different row(??) You might notice that the starting-row for the 3 named ranges seems to be 29 rows lower than wherever my cursor is. No idea what that could be about. But the chief weirdness is that the range reference would change AT ALL(!?)

(FYI: these 3 named ranges I use in a sorting function in VBA....and the weird thing is that I run the VBA every day, and it has always worked correctly; but this is weirding me out because I don't understand it. Any ideas?)

 

[d0rian]

I just noticed that it probably has something to do my not having used a $ anchor character in front of the row reference...
Still...I guess I'm surprised that:
(1) Name Manager doesn't consider ranges to be static, but rather dynamic based on what cell the user cursor is in; and
(2) My code has always run properly....(as far as I know); I may have just gotten lucky on that front though based on how I'm importing the data and how the VBA is set up.

Will leave this thread up even though main mystery is solved...?

 

[Alex Blakenburg]

You are correct the name manager allows relative referencing. I think we new functions such as Lambda and to a lesser extent Let this feature will get used a lot more.
I have only seen it done once pre-the new functions, but in principle you can put whole formulas in the name manager. (re: Jeff Lenning, Excel University)
This not dissimilar to how the new Lambda function is used.
This is aimed at making long and complex formulas in the spreadsheet more readable but relies on the addressing being relative to that you can copy it down to all rows (or columns).
I gave some examples in a previous post on this forum.

How is calculation made without seeing the formula