Copying formulas between workbooks

jappi

New Member
Joined
Sep 7, 2006
Messages
17
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jdavis9

Active Member
Joined
Mar 8, 2002
Messages
337
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.....
 

jappi

New Member
Joined
Sep 7, 2006
Messages
17
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 ?
 

jappi

New Member
Joined
Sep 7, 2006
Messages
17
Just to elaborate if its any use, the cells I am copying from just have an = reference to the other cell...nothing too complex
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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).
 

jdavis9

Active Member
Joined
Mar 8, 2002
Messages
337
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
 

jappi

New Member
Joined
Sep 7, 2006
Messages
17
Thanks guys - that seems to be one way of cleaning it up. I'll take it ! Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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