Excel 2007 labels in functions no longer supported!

[forAbetterWorld]

Unbelievable! What BoneHead would fix a wiring problem by axing the wire?
Labels have been a tool for proper programming since they technically,
thankfully could be supported. If some users do not want the benefit, then
make use of labels an option. More elegantly, Microsoft, fix the code
adapting to mis-use of labels in formulas by offering more graceful options
for users to continue the pursuit with Excel. Having built a complete and
reliable Accounting System depending on properly labeled cell references in
formulas (for audit and debug), i am asked as i open a 2003 file in the "NEW"
2007, TO SCRAP MY INVESTMENT IN PROPER SPREADSHEET PROGRAMMING, OR NOT USE
THE PRODUCT! UNBELIEVABLE !!!!!!!!!!!!! Now i must face the challenge of
uninstalling Excel 2007 and re-installing the more capable Excel 2003. This
tryanny will be addressed. I encourage your support and efforts to establish
an Act of Congress for Regulation of Software Civility. GOD save Earth!

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Bernard Liengme]

Microsoft knew from the day after the release of whatever version it was
(2000 or 2002, I think) that allowing labels to be used in formulas was a
big mistake. The writing was on the wall by XL2003 which had this feature
turned off by default. Many Excel writer have preached about the evils of
labels but some did not listen. There is no "fix" other than removing the
feature. Why not spend some time creating/defining Names?
best wishes

 

[Niek Otten]

<i must face the challenge of uninstalling Excel 2007 and re-installing the more capable Excel 2003>

I wouldn't do that if I were you.

Labels have always been warned for, mainly because they were poorly implemented.
Use Defined Names instead. You probably won't have to change one formula.
In the Formulas tab, Defined Names box, choose Create from selection.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Unbelievable! What BoneHead would fix a wiring problem by axing the wire?
| Labels have been a tool for proper programming since they technically,
| thankfully could be supported. If some users do not want the benefit, then
| make use of labels an option. More elegantly, Microsoft, fix the code
| adapting to mis-use of labels in formulas by offering more graceful options
| for users to continue the pursuit with Excel. Having built a complete and
| reliable Accounting System depending on properly labeled cell references in
| formulas (for audit and debug), i am asked as i open a 2003 file in the "NEW"
| 2007, TO SCRAP MY INVESTMENT IN PROPER SPREADSHEET PROGRAMMING, OR NOT USE
| THE PRODUCT! UNBELIEVABLE !!!!!!!!!!!!! Now i must face the challenge of
| uninstalling Excel 2007 and re-installing the more capable Excel 2003. This
| tryanny will be addressed. I encourage your support and efforts to establish
| an Act of Congress for Regulation of Software Civility. GOD save Earth!
|
| ----------------
| This post is a suggestion for Microsoft, and Microsoft responds to the
| suggestions with the most votes. To vote for this suggestion, click the "I
| Agree" button in the message pane. If you do not see the button, follow this
| link to open the suggestion in the Microsoft Web-based Newsreader and then
| click "I Agree" in the message pane.
|
|
http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Charles Williams]

Also look at Excel 2007 structured referencing to named tables.

regards
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[forAbetterWorld]

thank you for the opportunity to eat some crow. Pressing ahead, may i ask
what is meant by "label in formula". I started this game with Lotus Symphony
in 1982. Presumably "label" and "name" are not synonyms with respect to use
as cell reference in formula. Having pursued Help, i get no response to
search for "Labels in Formulas". So what is the basis of the threatening
message when opening a file: "Excel has detected that this file contains
labels in formulas. These are no longer supported in Excel 2007 and will be
replaced with cell references. Your formulas will continue to work
correctly. You cannot undo this change. Do you wish to continue?" Name
manager appears to be properly managing names which i took to be synonymous
with labels in the above imposing MS message. What is a "label" (as opposed
to chart label or axis label...) and how can i find, in an opened file,
opting Yes to the message, the basis incurring the message?

 

[Bob Umlas]

Suppose you have "Name" in cell A1 and "Bob" in A2. But you didn't define A2
to be called "Name". If you use "labels in formulas", then simply referring
to =NAME in a formula somewhere would have excel understand what you meant,
and will use cell A2, just as IF you named cell A2 "Name." In excel 2007, it
will simply change =NAME to =A2. Shouldn't be a problem.
Bob Umlas
Excel MVP

 

[Dana DeLouis]

Hi. In Excel 2003, you will find "Labels in Formulas" as one of the Excel's
Options. (Tools | Options. I've uninstalled 2003 at the moment).
I believe you will find the replacement in 2007 under:
"Office Button" | Excel Options | Formulas | and check "Use Table names in
formulas."

what is meant by "label in formula".

A simple example: Suppose A1 has the text "Price", and formatted differenly
than the cells below it. (Say Bold)
Suppose A2:A10 have numeric prices.
A formula in B5 can be =Price, and the value returned will be the value in
A5.
It makes reading a little easier (=Price vs = A5)
Apparnelty, on big projects, this auto feature must have caused problems.
It appears in 2007, that you must use Data Tables for this feature to work.
I suppose it's "better" ??

 

[Harlan Grove]

what is meant by "label in formula". . . .

....

If you had, say, a table in A15 that looked like

AA BB CC
w 1 1 0.42
x 1 2 0.72
y 2 1 0.08
z 2 2 0.19

You could immediately use formulas like

=w CC

=SUMIF(AA,1,CC)

without defining AA, BB, CC, w, x, y or z. Labels were effectively
implied range names. Much better to define names explicitly.

. . . So what is the basis of the threatening
message when opening a file: "Excel has detected that this file
contains labels in formulas. These are no longer supported in
Excel 2007 and will be replaced with cell references. Your
formulas will continue to work correctly. You cannot undo this
change. Do you wish to continue?" . . .

It's just a statement of fact.

Without a doubt it would have been MUCH BETTER for Microsoft to have
given users a choice whether to convert labels in formulas to cell
references *OR* leave them as-is but evaluate the labels as #NAME?
errors. Better still would have been offering to create defined names
like _LABEL_OriginalLabelHere and replacing the corresponding labels
in formulas with those newly created names. Likely some Excel
programmer proposed this, but more likely some manager decided this
wouldn't materially increase the $$$$ they could extract from the user
base, so it wasn't implemented.

. . . What is a "label" (as opposed to . . .

From Excel 2003 help:

'You can use the labels of columns and rows on a worksheet to refer to
the cells within those columns and rows. Or you can create descriptive
names to represent cells, ranges of cells, formulas, or constant
values. Labels can be used in formulas that refer to data on the same
worksheet; if you want to represent a range on another worksheet, use
a name.'

A label is a column or row heading. It's NOT a defined name, but it
functions similar to defined names in formulas, at least in older
versions.

. . . and how can i find, in an opened file, opting Yes to the
message, the basis incurring the message?

If you mean how can you find labels in files you open in Excel 2007,
you can't. They're replaced in formulas, and they no longer exist.
You'd have to open such an XLS file in Excel 2003, save all formulas
to a text file (not all that difficult with a macro), open the XLS
file in Excel 2007 agreeing to convert labels in formulas, save all
formulas to a different text file, then compare the two text files to
locate the differences, which would presumably only be due to label to
range reference conversions.

 

[Harlan Grove]

Microsoft knew from the day after the release of whatever version
it was (2000 or 2002, I think) . . .

It was in Excel 97, and I think it was in Excel 5, but I threw out my
Excel 5 disks long ago, so no way to check.

. . . that allowing labels to be used in formulas was a
big mistake. . . .

Since when does Microsoft acknowledge mistakes much less fix them? If
they ever fix mistakes, why won't they fix the MOD function, which
can't handle the full range of double precision floating point
operands that the hardware's FPREM1 (Wintel-centric) can handle? If
Microsoft could claim that fixing this bug would break backwards
compatibility, the same applies to labels in formulas.

. . . The writing was on the wall by XL2003 which had this
feature turned off by default. Many Excel writer have preached
about the evils of labels but some did not listen. There is no
"fix" other than removing the feature. . . .

Other than providing a conversion tool that would create defined names
from labels. If Excel 2007 is smart enough to be able to replace
labels in formulas with range references, it should also be smart
enough to have created defined names using the original labels
possibly with identifying text added to the name referring to those
ranges. That would still have removed the feature, but in a much more
intelligent, user-friendly way.

Why not spend some time creating/defining Names?

An excellent question to pose to the Excel developer team.

In my own cynical mind, I think Microsoft was too lazy to design the
Excel 2007 equivalent of the Excel 2003 & prior Label Ranges dialog,
and they couldn't come up with a clean way to stuff a command for it
into the ribbon. It's likely labels were stored in formulas in XLS
files in such a way that it was just easier to convert them to range
addresses than to rewrite the UI for labels. That, and there were
probably some name space collisions with tables and structured
referencing.

 

[Dana DeLouis]

Well, I suppose this is "better" ??

=Table1[[#This Row],[Price]]*2

but it was easier to read in 2003

=Price * 2

(Not using Named Ranges)

Changing subjects, if one does want to use Range Names, I don't see "Apply
Names" anywhere. I used that often in previous versions.
After 300 clicks on the "faster" Ribbon, I see I can add it to the "quick
access toolbar."
Funny, but my "quick acess toolbar" is getting very large.

 

[Bob Phillips]

Funny, but my "quick acess toolbar" is getting very large.

You and me both bro'.

 

[JLatham]

A 'Label in a Formula' is what we thought you were carrying on about - it was
a (poorly implemented and easily corrupted) way of using the heading you
entered in a column or left side of a row as a pointer to data within the
column/row.

A name is actually a named range that you create/define. Use those instead.
A name can refer to a single cell, a group of contiguous cells or even a
group of 'geographically' separated cells.

From Help on "Labels and Names in formulas" from Excel 2003 (edited)
"You can use the labels of columns and rows on a worksheet to refer to the
cells within those columns and rows. Or you can create descriptive names
(name: A word or string of characters that represents a cell, range of cells,
formula, or constant value. Use easy-to-understand names, such as Products,
to refer to hard to understand ranges, such as Sales!C20:C30.) to represent
cells, ranges of cells, formulas, or constant (constant: A value that is not
calculated and, therefore, does not change. For example, the number 210, and
the text "Quarterly Earnings" are constants. An expression, or a value
resulting from an expression, is not a constant.) values. Labels can be used
in formulas that refer to data on the same worksheet; if you want to
represent a range on another worksheet, use a name.
......
Note By default, Excel does not recognize labels in formulas. To use labels
in formulas, click Options on the Tools menu, and then click the Calculation
tab. Under Workbook options, select the Accept labels in formulas check box."

End of quote. As you see, "By default, Excel does not recognize labels in
formulas..." so an effort had to be made to do so.

 

[forAbetterWorld]

Thanks for all the loving replies. The construct should never have been
named "Label"; maybe "Presumed Label". I have never used them and i think
the untoward message infers a "Presumed Label" which in fact is not intended;
simply fortuitous arrangement . Symphony had a manuever to make use of a
fortuitous arrangement to convert a presumed label to a "name". That was
simply convenience. Excel, to my knowledge has never offered that
convenience creating a name. All better now... Beer's on me

 

[Harlan Grove]

. . . Symphony had a manuever to make use of a
fortuitous arrangement to convert a presumed label to a "name".
That was simply convenience. Excel, to my knowledge has never
offered that convenience creating a name. . . .

....

FWLIW, I too started off with Symphony. While it was a better
spreadsheet than character mode 123, it was a terrible word processor.

Anyway, try putting the following strings in B11 and A2:A5.

AA BB CC
w
x
y
z

Select the entire range A15, press [Shift]+[Ctrl]+[F3] or run the
menu command Insert > Name > Create, and Excel should display the
Create Names dialog with Top row and Left column boxes checked. Click
OK, and you have the following defined names.

AA B2:B5
BB C2:C5
CC D25
w B22
x B33
y B44
z B55

This has been part of Excel at least from the original Windows version
in the late 1980s. It's likely it was in the original Mac version from
the mid 1980s.

 

[Jon Peltier]

I have been reluctant to use the QAT, because I can't think of only 40 or so
buttons I'd want there, and it has seemed like a rather shoddy attempt to
make up for preventing easy user customization. My custom toolbars in 2003
must display over 125 buttons, and I can do what I want without changing the
annoying tabs to the one that has the command I really need now.

- Jon