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

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
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:

wait1.JPG


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:

wait2.JPG


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

wait3.JPG


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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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...?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top