Copyin Formulas containg Names

[BSc Chem Eng Rick]

I created a spreadsheet in an older version of Microsoft Excel (can't
remember exactly which one). The sheet contains formulas that use names to
refer to specific arrays, constants etc. Previously I was able to copy the
formula and it would retain the names. In the new Excel (2007) when I copy
the fomula, instead of the name being copied, the cell references are copied.
Furthermore, the references copy as relative references instead of absolute
references as they are defined in the names. When I try and edit the formula
the names automatically revert to their respective references (again
relative). I have tried retyping formulas from scratch with the names, same
problem. I have tried deleting an redefining the names, same problem. I then
created a new sheet in the same workbook and started using the names
arbitrarily in formulas (the names all ahve global scope to the entire
workbook) and then copying then to random cells. In this new sheet the
formulas retain the names and give the correct answers? Is there some strange
sheet property/option that I am overlooking?

 

[Jim Rech]

Maybe one of the Lotus compatibility options is set for that sheet? Office
button-> Excel Options -> Advanced - bottom of the list.

--
Jim
message |I created a spreadsheet in an older version of Microsoft Excel (can't
| remember exactly which one). The sheet contains formulas that use names to
| refer to specific arrays, constants etc. Previously I was able to copy the
| formula and it would retain the names. In the new Excel (2007) when I copy
| the fomula, instead of the name being copied, the cell references are
copied.
| Furthermore, the references copy as relative references instead of
absolute
| references as they are defined in the names. When I try and edit the
formula
| the names automatically revert to their respective references (again
| relative). I have tried retyping formulas from scratch with the names,
same
| problem. I have tried deleting an redefining the names, same problem. I
then
| created a new sheet in the same workbook and started using the names
| arbitrarily in formulas (the names all ahve global scope to the entire
| workbook) and then copying then to random cells. In this new sheet the
| formulas retain the names and give the correct answers? Is there some
strange
| sheet property/option that I am overlooking?

 

[BSc Chem Eng Rick]

Thanks Jim,

Both Lotus Compatibilty check boxes were selected for all existing sheets
and not for the new sheet. By removing the Lotus compatibilty all the names
in formulas are replaced by the relative references. What I did was select
the whole sheet and did a find/replace for each name (if there is a more
intelligent way to do this let me know) and now the sheet works perfectly.

Thanks again.

 

[Jim Rech]

if there is a more intelligent way to do this let me know

That's how I'd do it.

--
Jim
message | Thanks Jim,
|
| Both Lotus Compatibilty check boxes were selected for all existing sheets
| and not for the new sheet. By removing the Lotus compatibilty all the
names
| in formulas are replaced by the relative references. What I did was select
| the whole sheet and did a find/replace for each name (if there is a more
| intelligent way to do this let me know) and now the sheet works perfectly.
|
| Thanks again.
|
| "Jim Rech" wrote:
|
| > Maybe one of the Lotus compatibility options is set for that sheet?
Office
| > button-> Excel Options -> Advanced - bottom of the list.
| >
| > --
| > Jim
in
| > message | > |I created a spreadsheet in an older version of Microsoft Excel (can't
| > | remember exactly which one). The sheet contains formulas that use
names to
| > | refer to specific arrays, constants etc. Previously I was able to copy
the
| > | formula and it would retain the names. In the new Excel (2007) when I
copy
| > | the fomula, instead of the name being copied, the cell references are
| > copied.
| > | Furthermore, the references copy as relative references instead of
| > absolute
| > | references as they are defined in the names. When I try and edit the
| > formula
| > | the names automatically revert to their respective references (again
| > | relative). I have tried retyping formulas from scratch with the names,
| > same
| > | problem. I have tried deleting an redefining the names, same problem.
I
| > then
| > | created a new sheet in the same workbook and started using the names
| > | arbitrarily in formulas (the names all ahve global scope to the entire
| > | workbook) and then copying then to random cells. In this new sheet the
| > | formulas retain the names and give the correct answers? Is there some
| > strange
| > | sheet property/option that I am overlooking?
| >
| >
| >