Offset named ranges do not appear in Name Box

[JenniferMurphy]

If I define a named range that is the result of Offset, that name does not appear in the Name Box.

I assigned the name "Header" to B2. If I select B2, "Header" appears in the name box.

I then assigned the names V_1 & V_2 to B3 & B4 using Offset.

But these assigned names do not appear in the name box when selecting B3 or B4.

Also, why does the Name Manager show "{...}" for the value in these cells, rather than "V1" and "V2"?

 

[Fluff]

If I define a named range that is the result of Offset, that name does not appear in the Name Box.

AFAIK that has always been the case. Until the formula is calculated Xl has no way of knowing what the range is or what values it contains.

 

[JenniferMurphy]

AFAIK that has always been the case. Until the formula is calculated Xl has no way of knowing what the range is or what values it contains.

Really? When I open the Name Manager, all "calculations" are complete and all cells have their current values, no?

 

[Fluff]

Not when "Refers to" is a formula.

 

[JoeMo]

I tend to think of Header as a primary defined name, V_1 and V_2 as secondary in that they are relative to Header. If you move Header anywhere on the sheet (cut/paste), V_1 and V_2 move with it. You can reach them easily by pressing F5 to open the GoTo dialog box and entering V_1 or V_2 followed by Enter.

 

[JenniferMurphy]

Not when "Refers to" is a formula.

Yes, but it's a static formula -- one that is easily calculated by Name Manager.

 

[Fluff]

AFAIK the name manager doesn't calculate those formulae, it just shows what defined names you have got.

 

[JenniferMurphy]

I tend to think of Header as a primary defined name, V_1 and V_2 as secondary in that they are relative to Header. If you move Header anywhere on the sheet (cut/paste), V_1 and V_2 move with it. You can reach them easily by pressing F5 to open the GoTo dialog box and entering V_1 or V_2 followed by Enter.

I don't understand your point. Yes, named cells can move around and references move with them. But after they are moved, if they are moved, everything is static again. Excel ought to be able to show me the name that is assigned to any cell or range. After I set up these "referential" names, I'd like to be able to select the cells to check that I did it right. Excel is denying me this reasonable sanity check.

 

[Fluff]

After I set up these "referential" names, I'd like to be able to select the cells to check that I did it right.

You can, just follow the instructions from Joe.

 

[JoeMo]

I don't understand your point. Yes, named cells can move around and references move with them. But after they are moved, if they are moved, everything is static again. Excel ought to be able to show me the name that is assigned to any cell or range. After I set up these "referential" names, I'd like to be able to select the cells to check that I did it right. Excel is denying me this reasonable sanity check.

Try doing a cut/paste with V_1. The value will move but not the cell holding the name. As Fluff said, names having a formula in Refers To: don't appear in the Name box. But Excel provides another way to check their location by using the GoTo dialog box.