Copying a Column of Formulas between Excel Files

W

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
 
M

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.
 
P

Peo Sjoblom

Did you try that?


--
Regards,

Peo Sjoblom



Michael said:
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.




WillW said:
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
 
P

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
 
W

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.

Michael said:
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.




WillW said:
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
 
W

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.

Peo Sjoblom said:
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



WillW said:
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
 
P

Peo Sjoblom

I agree



--
Regards,

Peo Sjoblom



WillW said:
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.

Peo Sjoblom said:
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



WillW said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top