Copy Links

[Katiedid]

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!

 

[erik.van.geit]

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]

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]

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]

Dufus,

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

greetings,
Erik

 

[Katiedid]

You guys are great - both worked - have a f4ew more to go thanks a bunch!

Katiedid