Move formulas to another sheet, preserve references

nomar116

New Member
Joined
Jan 27, 2020
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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

New Member
Joined
Jan 27, 2020
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Aug 15, 2012
Messages
156
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

New Member
Joined
Jan 27, 2020
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Wow, yep! Thank you so much. Thank was an easy fix!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,101
Messages
5,628,696
Members
416,333
Latest member
Time2Learn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top