Can I copy a formula from one workbook to another without copying the file reference?

agwalsh
agwalsh used Ask the Experts™
on
Is there a way to copy formulas from one workbook to another WITHOUT copying the file reference and if so how? Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What's wrong with

Ctrl-c from first workbook

Ctrl-v to another workbook?
Does the source formula contain a reference to a worksheet or a workbook?

Author

Commented:
I tried that @Saqib - the source formula does contain a reference to a worksheet in the same workbook - but it would be the same worksheet name in the new file.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

In that case, either copy the formula from the formula bar or revert to VBA.

Unless someone has a better option.

Author

Commented:
Hm, yeah could be tricky as there are quite a few sheets...thanks though.
Roy CoxGroup Finance Manager

Commented:
Finance Analyst
Commented:
Couple of options:

Copy and paste as currently doing and then use Edit Links dialogue to correct file links.

Copy entire sheet with formula and sheet to which it refers to the new workbook, right click on sheet tab and select Move or Copy. This will keep the references between the sheets correct. The two sheets have to be copied at the same time to maintain the references. Select one sheet tab and then press Ctrl while selecting other sheet tab, then right click on one of those tabs.

Author

Commented:
Thank you to all of you as usual. EE rides to the rescue :-)
Roy CoxGroup Finance Manager

Commented:
I've just remembered there's an excellent free addin to find such unwanted links:

FINDLINK

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial