Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Copy worksheet with named formulas
- Thread starter Mezzzo
- Start date
TonyJollans
Programmer
Hi Mezzzo,
When you copy a sheet, all formulae should be copied. Exactly how they work on a new sheet may depend on how they reference other cells, but they should all be there. Could you give some examples of ones which don't work?
Enjoy,
Tony
------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
When you copy a sheet, all formulae should be copied. Exactly how they work on a new sheet may depend on how they reference other cells, but they should all be there. Could you give some examples of ones which don't work?
Enjoy,
Tony
------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
- Thread starter
- #3
I checked, it seems none of the formulas change as
expected. I named many cells that were part of the
formulas. I didn`t set the cells to absolute or relative.
I assume it not specified they are relative. In this case
are the references a problem?
I tried formula auditing on one cell. I see a small icon
that resembles a worksheet. I`ve never seen this before.
I`m wondering if this may have significance.
PS...None of the formats paste to the new sheet. Should
I select one of the paste options.
=entry_ht ........ this formula does not up date
Thanks for the help
expected. I named many cells that were part of the
formulas. I didn`t set the cells to absolute or relative.
I assume it not specified they are relative. In this case
are the references a problem?
I tried formula auditing on one cell. I see a small icon
that resembles a worksheet. I`ve never seen this before.
I`m wondering if this may have significance.
PS...None of the formats paste to the new sheet. Should
I select one of the paste options.
=entry_ht ........ this formula does not up date
Thanks for the help
TonyJollans
Programmer
Hi Mezzzo,
My apologies, I didn't really read your original question properly. Your names will probably reference absolute ranges and I think that, even if they reference relative ones, they will be copied as absolute.
The worksheet icon means you have a reference to another worksheet. Double-click on the line to it and it should bring up a GoTo dialog box with a list of all the references.
I'm sorry but I'm not giving you much help here. Time for me to step aside for someone who knows more about this.
As for the formats, there shouldn't be any problem. How are you doing the copy?
Enjoy,
Tony
------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
My apologies, I didn't really read your original question properly. Your names will probably reference absolute ranges and I think that, even if they reference relative ones, they will be copied as absolute.
The worksheet icon means you have a reference to another worksheet. Double-click on the line to it and it should bring up a GoTo dialog box with a list of all the references.
I'm sorry but I'm not giving you much help here. Time for me to step aside for someone who knows more about this.
As for the formats, there shouldn't be any problem. How are you doing the copy?
Enjoy,
Tony
------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
Named ranges can be global to the workbook or local to the sheet. So the same name can have more than one meaning within a workbook.
Almost certainly in your original sheet the names will be global.
Suppose we have a cell on that sheet named as TaxRate.
Scenario 1 is you select everything on the original sheet and paste into a new sheet. On the new sheet you will have references to TaxRate which are to the TaxRate cell on the original sheet - not to the cell on the new sheet that might have been the TaxRate cell.
Scenario 2 is you copy the whole worksheet with a Control Drag operation on the sheet tab. In this scenario, the new sheet will have its own TaxRate cell which is just local to that sheet.
Fundamentally, when you copy formulas and cells that include named ranges you have to decide whether the names are to be related back to the original sheet or whether the new sheet is to have its own private names.
If you go to Insert, Name, Define you will see a list of named ranges. If on the same line as the name there is a worksheet name then the named range is local to that worksheet.
If you have accidentally created local names you can delete them in which case the global names will take over.
Almost certainly in your original sheet the names will be global.
Suppose we have a cell on that sheet named as TaxRate.
Scenario 1 is you select everything on the original sheet and paste into a new sheet. On the new sheet you will have references to TaxRate which are to the TaxRate cell on the original sheet - not to the cell on the new sheet that might have been the TaxRate cell.
Scenario 2 is you copy the whole worksheet with a Control Drag operation on the sheet tab. In this scenario, the new sheet will have its own TaxRate cell which is just local to that sheet.
Fundamentally, when you copy formulas and cells that include named ranges you have to decide whether the names are to be related back to the original sheet or whether the new sheet is to have its own private names.
If you go to Insert, Name, Define you will see a list of named ranges. If on the same line as the name there is a worksheet name then the named range is local to that worksheet.
If you have accidentally created local names you can delete them in which case the global names will take over.
- Status
Similar threads
- Locked
- Question