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

 

[JenniferMurphy]

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.

Aha! I didn't get it the first time. You comment about moving the cells around confused me. I see now that I can use F5 to verify that V_1 is where I want it to be.

Still, I am not letting M$FT off the hook for not doing it the right (IMHO) way.

Thanks for the tip.

 

[JenniferMurphy]

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

Apparently, but it could and it should.

 

[JoeMo]

Aha! I didn't get it the first time. You comment about moving the cells around confused me. I see now that I can use F5 to verify that V_1 is where I want it to be.

Still, I am not letting M$FT off the hook for not doing it the right (IMHO) way.

Thanks for the tip.

You are welcome - thanks for the reply.

 

[Fluff]

Still, I am not letting M$FT off the hook for not doing it the right (IMHO) way.

You mean your way, not the "right way".
IMO you are wrong, the last thing I want, if I have multiple dynamic ranges, is for Xl to be recalculating them at the drop of a hat & killing my workbook.

 

[JenniferMurphy]

You mean your way, not the "right way".
IMO you are wrong, the last thing I want, if I have multiple dynamic ranges, is for Xl to be recalculating them at the drop of a hat & killing my workbook.

Yes, my way, of course -- same as you.

I'll admit that I don't understand much of the inner workings of Excel and I don't own a hat, but it seems to me that when I select a cell, any "recalculating" has been done and the sheet is static at that point. All Excel has to do is process that one formula in the assigned name. How much of a risk is that to "killing" your whole workbook?

ymmv

 

[Fluff]

For the names you have got, there wouldn't be much of a problem, but you often see dynamic ranges using a formula such as

=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)

especially when using data validation. If you had a lot of them it could cause problems.

 

[JenniferMurphy]

I just discovered another very bad (IMHO) side effect of Excel's failure to properly resolve formula-based cell names. If I select a cell that uses one of these named ranges, Excel highlights the base cell in the Offset function, not the actual cell.

"=V_1" references B3, not B2.

 

[JenniferMurphy]

For the names you have got, there wouldn't be much of a problem, but you often see dynamic ranges using a formula such as

=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)

especially when using data validation. If you had a lot of them it could cause problems.

Even if I had a million of them, Excel would only have to resolve the one in question, not all million.

 

[Fluff]

Excel does not "know" what cells an offset formula is looking at until it's evaluated, which is why it's a volatile function.