Help with Copy/Paste between Workbooks and Cell/Worksheet referenc

I have two workbooks with idential worksheet names. I need to copy a
formula from a worksheet in workbook A to the same relative worksheet in
workbook B. But I want the pasted cell/worksheet references in workbook B to
refer to the worksheet in workbook B and not back to the worksheet in
workbook A. If I do a simple copy/paste I get references back to worksheet
A. I see no way to do this with Paste Special. Any ideas?

Thanks in advance,

Bill

I just did a simple test making two workbooks, having a sheet in each book
names the same and copied a simple formula from the 1st workbook to the 2nd
and it just copied the formula and did not refer back to the 1st sheet. can
you give furhter data, as in what the formula is and what the actual cells
are that are being copied and pasted?

"Bill Viverette" wrote:

> I have two workbooks with idential worksheet names. I need to copy a
> formula from a worksheet in workbook A to the same relative worksheet in
> workbook B. But I want the pasted cell/worksheet references in workbook B to
> refer to the worksheet in workbook B and not back to the worksheet in
> workbook A. If I do a simple copy/paste I get references back to worksheet
> A. I see no way to do this with Paste Special. Any ideas?
>
> Thanks in advance,
>
> Bill

Open both WBs.

Select the *entire* original formula *IN THE FORMULA BAR*.
Right click in that selection and choose "Copy".

HIT <ENTER>.

Navigate to the target WB, right click in the desired cell, and choose
"Paste".
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Bill Viverette" <[email protected]> wrote in message
news:[email protected]...
I have two workbooks with idential worksheet names. I need to copy a
formula from a worksheet in workbook A to the same relative worksheet in
workbook B. But I want the pasted cell/worksheet references in workbook B
to
refer to the worksheet in workbook B and not back to the worksheet in
workbook A. If I do a simple copy/paste I get references back to worksheet
A. I see no way to do this with Paste Special. Any ideas?

Thanks in advance,

Bill

The formula is a concatenation of text from several cells:

=CONCATENATE('Data Sheet'!M47,". ",'Data Sheet'!M48,". ",'Data
Sheet'!M49,". ",'Data Sheet'!M50,". ",'Data Sheet'!M51,". ",'Data
Sheet'!M52,". ",'Data Sheet'!M53,". ",'Data Sheet'!M54,". ")

Not sure what you mean by the difference between the formula and "actual
cells
> are that are being copied and pasted".

Thanks,

Bill

"Tim M" wrote:

> I just did a simple test making two workbooks, having a sheet in each book
> names the same and copied a simple formula from the 1st workbook to the 2nd
> and it just copied the formula and did not refer back to the 1st sheet. can
> you give furhter data, as in what the formula is and what the actual cells
> are that are being copied and pasted?
>
> "Bill Viverette" wrote:
>
> > I have two workbooks with idential worksheet names. I need to copy a
> > formula from a worksheet in workbook A to the same relative worksheet in
> > workbook B. But I want the pasted cell/worksheet references in workbook B to
> > refer to the worksheet in workbook B and not back to the worksheet in
> > workbook A. If I do a simple copy/paste I get references back to worksheet
> > A. I see no way to do this with Paste Special. Any ideas?
> >
> > Thanks in advance,
> >
> > Bill

Yes, I believe that will help. I have several cells that I hoped to
cut/paste in one fell swoop, but if I can at least do one at a time that's a
start!

Thanks,

Bill

"RagDyeR" wrote:

> Open both WBs.
>
> Select the *entire* original formula *IN THE FORMULA BAR*.
> Right click in that selection and choose "Copy".
>
> HIT <ENTER>.
>
> Navigate to the target WB, right click in the desired cell, and choose
> "Paste".
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Bill Viverette" <[email protected]> wrote in message
> news:[email protected]...
> I have two workbooks with idential worksheet names. I need to copy a
> formula from a worksheet in workbook A to the same relative worksheet in
> workbook B. But I want the pasted cell/worksheet references in workbook B
> to
> refer to the worksheet in workbook B and not back to the worksheet in
> workbook A. If I do a simple copy/paste I get references back to worksheet
> A. I see no way to do this with Paste Special. Any ideas?
>
> Thanks in advance,
>
> Bill
>
>
>

Your OP stated a single formula.

To copy en-masse, you could "unformulate" all the formulas in a selection,
reverting them to simple text, then copy that selection to the target sheet,
and then change them back to XL recognizable formulas, where they'll retain
all their original cell references.

Since the equal sign ( = ) denotes a formula, select all the formulas that
you wish to copy, then:

<Edit> <Replace>
In "Find What", enter
=
In "Replace With", enter
^^
And Click on "Replace All".

Then, while the cells are *still* selected,
Right click in the selection and choose "Copy".
Navigate to the target sheet and paste wherever you wish.

Now, reverse the procedure to revert the selection to formulas.

Again, while the cells are *still* selected from the Paste,
<Edit> <Replace>
In "Find What", enter
^^
In "Replace With", enter
=
And Click on "Replace All".

This should give you what you're looking for.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bill Viverette" <[email protected]> wrote in message
news:[email protected]...
> Yes, I believe that will help. I have several cells that I hoped to
> cut/paste in one fell swoop, but if I can at least do one at a time that's
a
> start!
>
> Thanks,
>
> Bill
>
> "RagDyeR" wrote:
>
> > Open both WBs.
> >
> > Select the *entire* original formula *IN THE FORMULA BAR*.
> > Right click in that selection and choose "Copy".
> >
> > HIT <ENTER>.
> >
> > Navigate to the target WB, right click in the desired cell, and choose
> > "Paste".
> > --
> >
> > HTH,
> >
> > RD
> > =====================================================
> > Please keep all correspondence within the Group, so all may benefit!
> > =====================================================
> >
> > "Bill Viverette" <[email protected]> wrote in
message
> > news:[email protected]...
> > I have two workbooks with idential worksheet names. I need to copy a
> > formula from a worksheet in workbook A to the same relative worksheet in
> > workbook B. But I want the pasted cell/worksheet references in workbook
B
> > to
> > refer to the worksheet in workbook B and not back to the worksheet in
> > workbook A. If I do a simple copy/paste I get references back to
worksheet
> > A. I see no way to do this with Paste Special. Any ideas?
> >
> > Thanks in advance,
> >
> > Bill
> >
> >
> >