Copy Links

Katiedid

New Member
Joined
Sep 17, 2006
Messages
3
I have several workbooks/files that are set up the same, with cells in one worksheet linked to several other worksheets within the workbook/file.

I would like to copy the links from one workbook/file to another, but have the formulas that I copy refer to the worksheets within the new workbook that I am copying it to.

Is there any easy way, other than changing the referenced workbook in each cell?

Please help!!

Thanks!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Katiedid
Welcome to the Board !!!!!

workbook1 is named "Book1"
sheet1 A1 has
=Sheet2!A1

when you copy this cell to another workbook the formula will read
=[Book1]Sheet2!A1

you could use "replace" (Control+H)
put [Book1] in the 'what"box and nothing in the "replacement"
your formula will read again
=Sheet2!A1
of course "Sheet2" must exist in the new workbook

kind regards,
Erik
 

Katiedid

New Member
Joined
Sep 17, 2006
Messages
3
Thanks, Eric - I'll try it this morning - and thanks for the welcome - I'm a novice at anything other than basic Excel, so I am really excited to find this resource. You are kind to help - thanks!
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Katiedid,

I thought I'd toss another possible solution your way. When I have a lot of formulas to redirect to the active workbook, I use the Edit>Links menu and in the dialog box I select "Change Source" to point the source back to the workbook I'm changing. So for example if Book2 has copied formulas that refer to Book1, I change the source to Book2 in the dialog box. All external references are removed. Eric's method will allow you to pick which external references you wish to remove and the method I described will remove them all. Your choice.

Dufus
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Dufus,

that's the correct inbuiltfunction to use
thank you for the assist,

greetings,
Erik
 

Katiedid

New Member
Joined
Sep 17, 2006
Messages
3
You guys are great - both worked - have a f4ew more to go thanks a bunch!

Katiedid
 

Forum statistics

Threads
1,136,616
Messages
5,676,838
Members
419,653
Latest member
analyticalchemist94

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