Forum Discussion
How to copy formulas without...
How can I copy and paste the formulas from an existing spreadsheet into a new spreadsheet so that the formulas in the new sheet do not refer back to the existing sheet.
That depends, to some extent, on the formulas themselves, how the references in them are written. Do they, for example, include the names of tabs or sheets within themselves. A simple formula like =A1+B1 should be readily copied, but if there are more elaborate internal references, that could be causing the difficulty.
One thing you could do is, before copying, put an apostrophe before the equals sign in the existing spreadsheet's formula
'={existing formula}
copy that to the new spreadsheet and then remove the apostrophe.
Thank you so much for responding.
Just to be clear, the sheet is substantial. It has about fifteen tabs and there are dozens of cells with formulas in each tab.
When I do a copy and paste of an entire tab from the existing sheet, call it SHEET1 into a new sheet, call it SHEET2, every formula in SHEET2 begins with the reference to SHEET1.
For example, if the formula in a cell in SHEET1 is:
='Income'!BF7
Then the formula for that cell in SHEET2 shows up as:
='[SHEET1.xlsx]Income'!BF7
I want is the formula in SHEET2 to not have the [SHEET1.xlsx] component in it.
There's a nuance:
1. Copy desired formula from the formula bar.
2. Paste formula into formula bar of destination sheetI want
isthe formula in SHEET2 to not have the [SHEET1.xlsx] component in it.Did you read the last paragraph in my prior post? Here it is again.
One thing you could do is, before copying, put an apostrophe before the equals sign in the existing spreadsheet's formula
'={existing formula}
copy that to the new spreadsheet and then remove the apostrophe.
You might also try Find and Replace and replace each instance of the reference to the prior sheet with nothing.
- Thanks will try that.
