Copy and Paste Formula Without the linked file path
 

Hi, I regularly cut/paste Worksheet cells (with formula's) into a different
Workbook.

I want to keep the formulas "as is" and NOT have the linked workbook name
included in the formula.

Incorrect: ='[MasterFile.xls]Show Approval'!L111

Correct: ='Show Approval'!L111

Is there a paste feature to NO include the linked path in the formula?

Hi!

Try this:

Convert the cell formula that you want to copy into a text
string and copy that over to the other workbook. Once it's
pasted into the other workbook convert it back to a
formula. Then convert the cell that was copied back into a
formula.

To do that you simply edit the formula and precede it with
an apostrophie. Now it's a text string. Delete the
apostrophie and now it's back to being a formula.

This keeps excel from automatically changing the
references in formulas when copying.

Biff

-----Original Message-----
Hi, I regularly cut/paste Worksheet cells (with
formula's) into a different
Workbook.

I want to keep the formulas "as is" and NOT have the
linked workbook name
included in the formula.

Incorrect: ='[MasterFile.xls]Show Approval'!L111

Correct: ='Show Approval'!L111

Is there a paste feature to NO include the linked path in
the formula?
.

Kevin

Several methods.....

1. F2 to Edit. Precede the formula with an apostrophe then copy and paste to
new workbook. Delete the apostrophe from both cells.

2. F2 to Edit. Copy the formula from the formula bar. Switch to new
workbook and paste into the formula bar(not into the cell).

3. Use a macro to copy the formula(s) as is.

If you want to try 3. post back and I can post the code.


Gord Dibben Excel MVP



On Wed, 19 Jan 2005 17:57:01 -0800, KevinB
wrote:

Hi, I regularly cut/paste Worksheet cells (with formula's) into a different
Workbook.

I want to keep the formulas "as is" and NOT have the linked workbook name
included in the formula.

Incorrect: ='[MasterFile.xls]Show Approval'!L111

Correct: ='Show Approval'!L111

Is there a paste feature to NO include the linked path in the formula?