View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove Harlan Grove is offline
external usenet poster
 
Posts: 733
Default Excel 2007 labels in functions no longer supported!

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

....

If you had, say, a table in A1:D5 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.