Copy & Paste formula

[bailbonds]

Hi,

When I copy a cell with formula from one workbook to another, if the formula contains links to cells in other worksheet then the paste function copies the formula with those same original links.

i.e
Original formula in workbook "Maths1" =('test1'!a1/'test2'!a1).

I then want this exact formula in another workbook "Maths2" but it copies it as -

=(['Maths1.xls]test1'a1/['Maths1.xls]test2'a1)

How can I paste it so it just does --> =('test1'!a1/'test2'!a1)

Thanks

 

[Jonmo1]

After you've copy pasted the formula, and it has the link to the old book...

Go to Edit - Links
Select the Maths1.xls and click Change Source
Browse to and select Maths2.xls


Hope that helps.

 

[Andrew Poulsom]

You can't, but after pasting you can Edit|Links and change the Source to the target workbook (using the Browse button).

 

[danonuke]

Hi

If it's just one formula, double-click to edit the cell, select it with the mouse (like you're selecting text in Word) and then paste into the new spreadsheet.

If it's several formulas, copy and paste as normal and then use Find & Replace to get rid of the workbook references. In this case, replace ['Maths1.xls]test1' with 'test1' and repeat for test2. (In fact, looking at your post, you might need to include ! in your replacement text, but I guess that's a typo?)

cheers
dan

 

[bailbonds]

Cheers all,

I've selected change source which opens up my drives where I can select the worksheet. When selected, however, it returns the mesaage

"Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again."

 

[Andrew Poulsom]

Do you have worksheets named test1 and test2 in Maths2.xls?

 

[bailbonds]

No, Test3 & Test4. However the sheet that I'm pasting from and to is named the same.

 

[Andrew Poulsom]

But your formula references test1 and test2.

 

[bailbonds]

Yes.

I guess this means I need to name the worksheets exactly the same?

 

[Andrew Poulsom]

Yes.

I guess this means I need to name the worksheets exactly the same?

Of course.