Copyin Formulas containg Names
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? |
Copyin Formulas containg Names
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 "BSc Chem Eng Rick" <BSc Chem Eng wrote 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? |
Copyin Formulas containg Names
if there is a more intelligent way to do this let me know
That's how I'd do it. -- Jim "BSc Chem Eng Rick" wrote in 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 | "BSc Chem Eng Rick" <BSc Chem Eng wrote 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? | | | |
All times are GMT +1. The time now is 02:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com