Move formulas to another sheet, preserve references

[nomar116]

I have a spreadsheet with a lot of data that I basically want to break into two sheets within the same workbook. I'm trying to move a collection of cells from the original sheet to a new sheet. Those cells contain mostly formulas that the original sheet's cells only by cell name, i.e. they do not include a sheet reference since they're currently all in the same worksheet.

I've been able to do this when copying the data to a new workbook, but not a new sheet within the same workbook. Any suggestions are welcome!

 

[rondeondo]

Hi @nomar116
if you cut and paste from the original sheet to another sheet the formulas will be re-written with the original sheet refs. If you copy&paste the formulas are relative to where you paste.

 

[nomar116]

Hmm, when I try any of the copy and paste options, my Excel (2013) is not looking back to the original sheet. Instead, the formula copies over exactly as written and creates a circular reference. This circular reference is because I'm pasting the formulas in the same cells as the referenced data of the original sheet.

 

[rondeondo]

Hi
That's right, copy & paste works differently to cut & paste. Try Cut, it's moving the contents rather than making a copy. It's excel logic at its finest.

 

[nomar116]

Wow, yep! Thank you so much. Thank was an easy fix!