Excel 2008 not compatible with named cells?

[Constantin]

For work, I depend on creating excel files with hundreds of cell name references. These references allow me to use plain english names for variables rather than hardcoded cell references (i.e. "Labor_Rate" vs. "Worksheet1!A:2").

When I open a Excel '97 file from my work computer in Mac Office 2008, the file is fine initially - all calculations work, all references are OK. However, if I duplicate a worksheet within the workbook that refers to these fields, then all references on some (but not all) sheets get wiped out. From what I can tell, the cell references in the leftmost worksheet remain valid, the references to cells in the remaining worksheets get broken.

Note, I am not copying a worksheet that contains these references. I am simply copying a worksheet that depends on them. As a result of this error, my entire workbook goes from executing fine to being filled with REF! errors. This was not a problem under Office 2004 for the Mac. There is no VB involved either...

Any suggestions what might be causing this and how to work around it (other than reverting to Office 2004)? Alternatively, if someone could point me in the right direction re: reporting bugs to the Mac BU, that would be great also. Oh, and if it's any help, I'm running 10.5.1 with all the patches on an Intel-based MacBookPro.

 

[JE McGimpsey]

For work, I depend on creating excel files with hundreds of cell name
references. These references allow me to use plain english names for
variables rather than hardcoded cell references (i.e. "Labor_Rate" vs.
"Worksheet1!A:2").

That's *generally* the best way to do it with tons of references - makes
auditing much easier.

When I open a Excel '97 file from my work computer in Mac Office 2008, the
file is fine initially - all calculations work, all references are OK.
However, if I duplicate a worksheet within the workbook that refers to these
fields, then all references on some (but not all) sheets get wiped out. From
what I can tell, the cell references in the leftmost worksheet remain valid,
the references to cells in the remaining worksheets get broken.<br>

Not sure what's going on her, but have a couple of questions...

With the sheet that works active, are the names all workbook level or
are some sheet level (i.e, in Insert/Name/Define dialog, do any of the
names have the sheet name attached - e.g., Sheet1!MyName)?

Are the problem names all just cell references, or are some of them
calculations?

When you scroll through the names in that dialog, do the problem names
show a Workbook reference (e.g., [Workbook1.xls]sheet1!myname) in the
Refers To: input box?

In the sheet with the errors, does #REF! appear in the formulae
themselves, or just the results?

Note, I am not copying a worksheet that contains these references. I am
simply copying a worksheet that depends on them. As a result of this error,
my entire workbook goes from executing fine to being filled with REF! errors.
This was not a problem under Office 2004 for the Mac. There is no VB involved
either...<

Are you copying via CMD-c/Cmd-v to a blank sheet, or via Edit/Move or
Copy worksheet?

Are you copying to a new sheet within the same workbook/file or to a new
one?

Any suggestions what might be causing this and how to work around it (other
than reverting to Office 2004)? Alternatively, if someone could point me in
the right direction re: reporting bugs to the Mac BU, that would be great
also. Oh, and if it's any help, I'm running 10.5.1 with all the patches on an
Intel-based MacBookPro.

Report bugs via the Help/Send Feedback... item in any Office app.

 

[Constantin]

Thanks for the quick answer - I apologize for the late reply, I just got home from a long day of travel. To answer your questions:

1) All cell 'names' are workbook level, none are worksheet level.
2) Some of these names may refer to a cell that contains the result of calculations. However, this doesn't seem to be a problem - the first sheet in the workbook (for which the cell references continue to work) has such cells.
3&4) I'll have to get back to you on those tomorrow... too tired!
5) I am duplicating the worksheet by right-clicking the tab and selecting 'move/copy', followed by asking Excel to create a duplicate.
6) All worksheets remain in the same workbook. Also, the cell name references die for all dependent worksheets, not just the new one. The original reference (i.e. the worksheet with part cost database) shows no names for any of the cells...

I will do my best to answer your remaining questions in the morning. Many thanks in the meantime. C.

 

[Constantin]

Good Morning,

I re-opened one of the problem workbooks. As you suggested, the problem targets cells with names with calculations. All other cells (i.e. the ones that just contain a numeric value) continue to reference fine, all across the workbook. Thus, please disregard my observation #2 above re: some worksheets with names working better than others... all cells with calculations in them lose their reference names when I duplicate a worksheet in that workbook.

The calculations within the worksheets that depend on these references seem to be OK, that is there are no REF! errors in the formulas that refer to the missing references... the formulas simply report a REF error because the cell references themselves are gone. That is, it is as if you write a formula with undefined or misspelled cell references.

Looking through the INSERT-NAME-DEFINE list of named cells, most of my cell references are gone. All of them contained calculations or were dependent other references (i.e. they were set to be equal to a adjacent cell, for example, which is a sort of calculation, I suppose).

If I insert a blank worksheet into the workbook and then copy the contents of another worksheet into it, no cell name references get broken and the worksheet/workbook continues to function fine. Thus, there seems to be some sort of worksheet duplication bug that affects the cell name references for all cells that contain calculations whenever someone uses the "copy worksheet" function.

 

[Bob Greenblatt]

Good Morning,

I re-opened one of the problem workbooks. As you suggested, the problem
targets cells with names with calculations. All other cells (i.e. the ones
that just contain a numeric value) continue to reference fine, all across the
workbook. Thus, please disregard my observation #2 above re: some worksheets
with names working better than others... all cells with calculations in them
lose their reference names when I duplicate a worksheet in that workbook.

The calculations within the worksheets that depend on these references seem to
be OK, that is there are no REF! errors in the formulas that refer to the
missing references... the formulas simply report a REF error because the cell
references themselves are gone. That is, it is as if you write a formula with
undefined or misspelled cell references.

Looking through the INSERT-NAME-DEFINE list of named cells, most of my cell
references are gone. All of them contained calculations or were dependent
other references (i.e. they were set to be equal to a adjacent cell, for
example, which is a sort of calculation, I suppose).

If I insert a blank worksheet into the workbook and then copy the contents of
another worksheet into it, no cell name references get broken and the
worksheet/workbook continues to function fine. Thus, there seems to be some
sort of worksheet duplication bug that affects the cell name references for
all cells that contain calculations whenever someone uses the "copy worksheet"
function.

Yep! Looks like a problem. I¹ll report it.

 

[Constantin]

Thanks Bob, I appreciate it.