Copy entire worksheet formulae

[books4cars]

I want to copy an entire worksheet's formulas to another worksheet without reference to the original worksheet.

For instance when I paste special formaulas only, I get this:
='[PAYROLL 03-2011b.xlsx]WEEK F'!AT$23

I want it without the '[PAYROLL 03-2011b.xlsx]

It should just say =WEEK F'!AT$23

Thanks

 

[cgcamal]

Hi,

This happens when you use paste special with the option "Paste Link" within same same workbook or from another workbook (in this case will add the workbook name) as in your example.

Try only paste special-->"All" or paste special-->"Formulas" and see what happen, or a simple Ctrl+C and then Ctrl+V.

Regards

 

[books4cars]

Hi,

This happens when you use paste special with the option "Paste Link" within same same workbook or from another workbook (in this case will add the workbook name) as in your example.

Try only paste special-->"All" or paste special-->"Formulas" and see what happen, or a simple Ctrl+C and then Ctrl+V.

Regards

Originally I had done it with paste special formulas and straight Ctrl V. I just tried paste special All, but it still links to the original workbook.

Thanks

 

[brettdj]

If your second workbook has the same sheet structure as the source workbook - ie same sheet names (WEEK F) - then you can just relink the second workbook to itself to remove the external references (via Date .. Edit Links in xl2010)

Cheers

Dave

 

[books4cars]

If your second workbook has the same sheet structure as the source workbook - ie same sheet names (WEEK F) - then you can just relink the second workbook to itself to remove the external references (via Date .. Edit Links in xl2010)

Cheers

Dave

The sheet is the same structure so the week f issue is ok. I want to paste without the "[PAYROLL 03-2011b.xlsx]".

Thanks

 

[brettdj]

You cant

The simple workaround is to paste as is then remove the references to [PAYROLL 03-2011b.xlsx] by changing the workbook links from [PAYROLL 03-2011b.xlsx] to itself (unlss there are vaid PAYROLL 03-2011b.xlsx references that you want to keep)

so if the workbook you were pasting to was Book1.xlsx

1) Paste formulae from PAYROLL 03-2011b.xlsx to Book1.xlsx
2) Edit Links in Book1.xlsx by chanfing any links from PAYROLL 03-2011b.xlsx to Book1.xlsx

Cheers

Dave

 

[Armando Montes]

Try
Find =
Replace with # or similar
Copy/Paste
Find #
Replace with =