Copying formulas between workbooks

jappi

New Member
Joined
Sep 7, 2006
Messages
26
Office Version
  1. 365
Platform
  1. MacOS
Can anyone tell me how to do this ?

I have one file with multiple tabs for financial transactions by each day of the week. In this workbook is also a summary tab which is referencing specific values (cells) in the other tabs.

I have a bunch of other workbooks that have all the daily transactions in exactly the same format as the other workbook but the other files don't have the summary tab.

So...I want to add the summary tab to the other workbooks and then just copy and paste all the summary references over. When I do this now it brings over the formulas but they all reference the tabs in the other file. Want to reference within files not between files.....hope that makes sense.

Thanks.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Instead of copying the sheet by selecting all and doing cntrl c, right click the tab select move or copy, select the book to move it to, select create copy and ok. I just tried it, and the references did not stay linked to the source workbook it came from.....
 
Upvote 0
Thanks for the prompt response. I have followed your instructions specifically but I'm sorry to say the references to the other workbook remain. Plan B ?
 
Upvote 0
Just to elaborate if its any use, the cells I am copying from just have an = reference to the other cell...nothing too complex
 
Upvote 0
perhaps after you have followed jdavis9's instructions you can perform a Find & Replace (from the Edit menu) and just replace the workbook name with nothing (i.e. leave the Replace field blank).
 
Upvote 0
That is the only thing I can find to make work if it is keeping the link.

Do a find replace [otherfile.xls] with for the sheet you transfered.

=[Book1.xls]s2!A1 becomes =s2!a1
 
Upvote 0
Thanks guys - that seems to be one way of cleaning it up. I'll take it ! Thanks again
 
Upvote 0

Forum statistics

Threads
1,223,289
Messages
6,171,228
Members
452,391
Latest member
BHG

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
Back
Top