Excel 2007 conversion - this file contains labels in formulas

[Guest]

Opening an Excel 2002 file in Excel 2007 for the first time, I'm greeted with:

"Excel has detected that this file contains lables 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 want to continue?"

Okay, fine. But after I open up the file, how can I tell which cells have
been changed by Excel? Is there a way I can find these formulas in an older
Excel version? I'd like to replace these with cell names not cell
references...

I'd rather not go for the eyeball approach - the file is 6MB with 24 tabs ...

 

[loudfish]

Opening an Excel 2002 file in Excel 2007 for the first time, I'm greeted with:

"Excel has detected that this file contains lables 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 want to continue?"

Okay, fine. But after I open up the file, how can I tell which cells have
been changed by Excel? Is there a way I can find these formulas in an older
Excel version? I'd like to replace these with cell names not cell
references...

I'd rather not go for the eyeball approach - the file is 6MB with 24 tabs ...

Easiest if you still have access to a machine with Excel 2002, and
open the original file. Turn off the names in formulas setting (in
tools options calculation).

Cells where they were used should now turn to #NAME errors - which if
there are only a few, you can search with
edit...goto...special..formulas...errors.

HTH

Andrew

 

[Guest]

When I try to turn off that setting in Excel 2003 (I don't have 2002
anymore), it immediately prompts with a similar message about converting the
formulas right there on the spot. So I would never get to search for errors.

Eventually here's the approach I took (using Excel 2003):
- I created an identical copy of the workbook, but in the new copy I
turned off names in the formula setting (thanks for the tip Andrew) which
triggered the automatic formula change. I saved the new copy.

- I then went to a web search engine and searched "vba compare workbooks"
which took me to some snappy macro to compare worksheets lickety split.

- Then it was just a matter of comparing the workbooks to find the culprit.

 

[Bob Flanagan]

Greetings Hunter. Am I correct to understand that one can not use range
names in formulas in Excel 2007? Or is this something else? confused.

Bob Flanagan

 

[Debra Dalgleish]

Range names are supported in Excel 2007. In earlier versions of Excel,
you can use Natural Language formulas, as explained here:

http://support.microsoft.com/kb/279412

 

[Guest]

I believe the question referred to so-called natural language names, which
are not supported in XL 2007. Named ranges, however, are a different beast
entirely, are not prone to confusion or error, and are supported in XL 2007.

Dave

 

[Bob Flanagan]

Thanks Dave and Debra for the clarification.

Bob Flanagan