Copying a Column of Formulas between Excel Files

[WillW]

I'd like to be able to copy a column of hyperlink formjula cells, e.g.,
=HYPERLINK('P1'!$B$49,'P1'!$A$49), from one Excel file to another, leaving
the formulas as they are in the original file.

The default seems to be for Excel to cleverly add a reference to the
original file into the formula, =HYPERLINK('[PIID
Worksheet-projects1.xls]P1'!$B$49,'[PIID Worksheet-projects1.xls]P1'!$A$49)

But how can I get an exact copy of my column of formulas from one file to
another?

Thanks,
Bill

 

[Michael]

When pasting to the target file use the Paste Special option selecting
Fromulas only, and if you also need the formatting do that as a second step.

 

[Peo Sjoblom]

Did you try that?


--
Regards,

Peo Sjoblom

When pasting to the target file use the Paste Special option selecting
Fromulas only, and if you also need the formatting do that as a second
step.

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

I'd like to be able to copy a column of hyperlink formjula cells, e.g.,
=HYPERLINK('P1'!$B$49,'P1'!$A$49), from one Excel file to another,
leaving
the formulas as they are in the original file.

The default seems to be for Excel to cleverly add a reference to the
original file into the formula, =HYPERLINK('[PIID
Worksheet-projects1.xls]P1'!$B$49,'[PIID
Worksheet-projects1.xls]P1'!$A$49)

But how can I get an exact copy of my column of formulas from one file to
another?

Thanks,
Bill

 

[Peo Sjoblom]

Try this, in the workbook with the original hyperlink formulas do
edit>replace (or ctrl +h)
find what =

replace with ^=^

so replace the equals sign with a dummy text string not likely to be in any
of the formulas

select the replaced formulas which are now regular text strings, copy and
paste them into the new workbook. Finally reverse it

find what ^=^

replace with =

in both workbooks

 

[WillW]

Thanks, Michael -- this was the first thing I tried. But it does not prevent
the file name from being added to the formulas when I paste the column into
the second file.

When pasting to the target file use the Paste Special option selecting
Fromulas only, and if you also need the formatting do that as a second step.

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

I'd like to be able to copy a column of hyperlink formjula cells, e.g.,
=HYPERLINK('P1'!$B$49,'P1'!$A$49), from one Excel file to another, leaving
the formulas as they are in the original file.

The default seems to be for Excel to cleverly add a reference to the
original file into the formula, =HYPERLINK('[PIID
Worksheet-projects1.xls]P1'!$B$49,'[PIID Worksheet-projects1.xls]P1'!$A$49)

But how can I get an exact copy of my column of formulas from one file to
another?

Thanks,
Bill

 

[WillW]

Thanks very much. This works.

I often wished for a new option on Paste Special: Exact, i.e., formulas - or
whatever - would be pasted exactly as they exist in the original cell. In
many cases, this could eliminate the use of $ and would certainly help in my
current situation.

Try this, in the workbook with the original hyperlink formulas do
edit>replace (or ctrl +h)
find what =

replace with ^=^

so replace the equals sign with a dummy text string not likely to be in any
of the formulas

select the replaced formulas which are now regular text strings, copy and
paste them into the new workbook. Finally reverse it

find what ^=^

replace with =

in both workbooks



--
Regards,

Peo Sjoblom

I'd like to be able to copy a column of hyperlink formjula cells, e.g.,
=HYPERLINK('P1'!$B$49,'P1'!$A$49), from one Excel file to another, leaving
the formulas as they are in the original file.

The default seems to be for Excel to cleverly add a reference to the
original file into the formula, =HYPERLINK('[PIID
Worksheet-projects1.xls]P1'!$B$49,'[PIID
Worksheet-projects1.xls]P1'!$A$49)

But how can I get an exact copy of my column of formulas from one file to
another?

Thanks,
Bill

 

[Peo Sjoblom]

I agree



--
Regards,

Peo Sjoblom

Thanks very much. This works.

I often wished for a new option on Paste Special: Exact, i.e., formulas -
or
whatever - would be pasted exactly as they exist in the original cell. In
many cases, this could eliminate the use of $ and would certainly help in
my
current situation.

Try this, in the workbook with the original hyperlink formulas do
edit>replace (or ctrl +h)
find what =

replace with ^=^

so replace the equals sign with a dummy text string not likely to be in
any
of the formulas

select the replaced formulas which are now regular text strings, copy and
paste them into the new workbook. Finally reverse it

find what ^=^

replace with =

in both workbooks



--
Regards,

Peo Sjoblom

I'd like to be able to copy a column of hyperlink formjula cells, e.g.,
=HYPERLINK('P1'!$B$49,'P1'!$A$49), from one Excel file to another,
leaving
the formulas as they are in the original file.

The default seems to be for Excel to cleverly add a reference to the
original file into the formula, =HYPERLINK('[PIID
Worksheet-projects1.xls]P1'!$B$49,'[PIID
Worksheet-projects1.xls]P1'!$A$49)

But how can I get an exact copy of my column of formulas from one file
to
another?

Thanks,
Bill