Copying formulas to different spreadsheet

[Tosty]

Good morning everyone. I have a question I am hoping someone can answer. I have some formulas in a spreadsheet that I want to use in a different (but identical) spreadsheet. The problem is; when I copy the formulas and paste them into the new spreadsheet, the formulas now have references to the name of the previous spreadsheet, which I don't want to reference any longer. I just want to copy the formulas to the new spreadsheet without the references to the old spreadsheet. Is there a way to do it so I don't have to get into each formula after I have copied them and delete the references to the name of the old spreadsheet? I have looked around in tools/options and "paste special", but I haven't found anything helpful there. Thank you in advance for any help you can provide.

Best regards,
Tosty

 

[Saagar]

It might do that if there are absolute references in the formula. Reply with the formula and the name of the origin spreadsheet.

 

[Tosty]

Hi Saagar, thank you for responding. You are correct. I guess I left out some important information. The formulas do have absolute cell references, and they also refer to a different "tab" within that spreadsheet. So when I try to copy them over into my new spreadsheet, it wants to reference the cells in that old spreadsheet. Thanks for any help you can provide.

Best regards,
Tosty

 

[Saagar]

From what I know, instead of copying the origin cell, do this:
1. Select origin cell
2. Click F2
3. Select whole text in cell and copy
4. Hit "Esc" key
4. Select destination cell
5. Paste

 

[Tosty]

Thanks Saagar, I appreciate the info, and I think that will work, but do you know if there is a way to do that with multiple cells at a time or will they all have to be done separately?

Best regards,
Tosty

 

[juxey2000]

I use a neat little trick for this one.

Do a 'find and replace all' on "=" and replace it with "~" or some other random symbol. This will break all the formulas in the sheet and make them visible. Do a 'select all', copy, then 'paste special', 'formulas' into the new sheet. One you have done this do a find replace for "~" or whatever symbol you used and replace it back to "=" This will fix all the formulas again and should work.

Simples

 

[Tosty]

Thanks Juxey2000, I'll give it a try!

Best regards,
Tosty