I have a workbook with 4 worksheets which each include a column of
numbers with the string "Old Numbers" (say) as a label at the top of
that column of numbers.

11 rows above is the formula;
=Max(Old Numbers)

I have a number of workbooks based on this useage, all of which
function perfectly.

I have "Accept labels in Forumulas" checked - that is, it is on and
functioning.

In this particular spreadsheet, 2 workbooks evaluate the formula
correctly, 2 do not.

For the 2 istances currently working, if I F2 the cell with the
formula, the range used is outlined in blue, but extends to row 16421
when I only need up to 2941. If I then press escape, the currently
working formula continues to work.

If I F2 a currently working formula and press enter, it stops working
and shows a #NAME? error in the cell, just like the 2 which don't
work. That is how the 2 which don't work came not to work. Usually,
F2, Enter simply changes the outlined range to the first contiguous
group of cells under the label.

Another procedure;
Ctrl, End, then delete every row back to the rows actually in use
Save
Close
Open
F2 a working formula

The outlined range is now only to row 2941, but again, an F2,Enter
renders it erroneous, as above.

Any clues gratefully received.

Has anyone experienced any other oddities with this feature?

Anything at all. Maybe you sorted it, maybe not, just any insight may
help me from reconstructing a massive spreadsheet.

On May 5, 1:20*pm, wrote:
I have a workbook with 4 worksheets which each include a column of
numbers with the string "Old Numbers" (say) as a label at the top of
that column of numbers.

11 rows above is the formula;
=Max(Old Numbers)

I have a number of workbooks based on this useage, all of which
function perfectly.

I have "Accept labels in Forumulas" checked - that is, it is on and
functioning.

In this particular spreadsheet, 2 workbooks evaluate the formula
correctly, 2 do not.

For the 2 istances currently working, if I F2 the cell with the
formula, the range used is outlined in blue, but extends to row 16421
when I only need up to 2941. *If I then press escape, the currently
working formula continues to work.

If I F2 a currently working formula and press enter, it stops working
and shows a #NAME? error in the cell, just like the 2 which don't
work. *That is how the 2 which don't work came not to work. *Usually,
F2, Enter simply changes the outlined range to the first contiguous
group of cells under the label.

Another procedure;
Ctrl, End, then delete every row back to the rows actually in use
Save
Close
Open
F2 a working formula

The outlined range is now only to row 2941, but again, an F2,Enter
renders it erroneous, as above.

Any clues gratefully received.

Public Service Announcement
If you get this problem - here's what to do.
1. Change your label -e.g. call it "Test"
2. Change your in-cell formula to use the new label
3. Change the label back to what you really want. Your formula should
automatically change at the same time and begin working correctly.

I read somewhere that this "use labels as names" feature is roughly
akin to interpreted code, that is, it's compiled on the fly each time
it needs to be evaluated, which reportedly provides Excel lots of
chances to get confused. Not sure where I saw that now.

So I was testing the feature on a new worksheet and found that even a
new worksheet could not use a new instance of the label as a name. A
bit of playing and the above recipe began de-confusing the
application.

Hope this saves you a pile of time sometime.


On May 5, 1:20*pm, wrote:
I have a workbook with 4 worksheets which each include a column of
numbers with the string "Old Numbers" (say) as a label at the top of
that column of numbers.

11 rows above is the formula;
=Max(Old Numbers)

I have a number of workbooks based on this useage, all of which
function perfectly.

I have "Accept labels in Forumulas" checked - that is, it is on and
functioning.

In this particular spreadsheet, 2 workbooks evaluate the formula
correctly, 2 do not.

For the 2 istances currently working, if I F2 the cell with the
formula, the range used is outlined in blue, but extends to row 16421
when I only need up to 2941. *If I then press escape, the currently
working formula continues to work.

If I F2 a currently working formula and press enter, it stops working
and shows a #NAME? error in the cell, just like the 2 which don't
work. *That is how the 2 which don't work came not to work. *Usually,
F2, Enter simply changes the outlined range to the first contiguous
group of cells under the label.

Another procedure;
Ctrl, End, then delete every row back to the rows actually in use
Save
Close
Open
F2 a working formula

The outlined range is now only to row 2941, but again, an F2,Enter
renders it erroneous, as above.

Any clues gratefully received.