Do not want formulas to reference old workbook.

[dicko1]

I am creating a copy of a worksheet into a new workbook and all of the
formulas automatically reference the old workbook.

For example:

Formula in Old Workbook is

=R9*L17+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

Once I create a copy of the worksheet which contains this formula and
others like it into a new workbook, the formula automatically changes
too

=R9*L17+GETPIVOTDATA("Billing Amount",'[oldworkbook.xls]Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

I just want the same formula to be copied to the new workbook, not pull
from the old workbook.

Please help.
Ryan

 

[Gord Dibben]

One method which always works is.........

Before copying to new workbook do an Edit>Replace

what: =

with: ^^^^

Replace all.

Copy to the new workbook and reverse the replace process.

Close source workbook without saving or reverse the replace there also.

I find just closing without save is easiest for me.


Gord Dibben MS Excel MVP

 

[Guest]

You have links. I use Copy > Paste Special > Formulas.

 

[dicko1]

Gord,

Great idea, I was overthinking it. Some of the formulas are over 255
characters so they wont transfer properly, but its better than updating
100 of links.

Thanks,
Ryan

One method which always works is.........

Before copying to new workbook do an Edit>Replace

what: =

with: ^^^^

Replace all.

Copy to the new workbook and reverse the replace process.

Close source workbook without saving or reverse the replace there also.

I find just closing without save is easiest for me.


Gord Dibben MS Excel MVP

I am creating a copy of a worksheet into a new workbook and all of the
formulas automatically reference the old workbook.

For example:

Formula in Old Workbook is

=R9*L17+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

Once I create a copy of the worksheet which contains this formula and
others like it into a new workbook, the formula automatically changes
too

=R9*L17+GETPIVOTDATA("Billing Amount",'[oldworkbook.xls]Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

I just want the same formula to be copied to the new workbook, not pull
from the old workbook.

Please help.
Ryan

 

[Guest]

I think the easiest solution would be to copy the sheets that are 'linked to'
at the same time as the sheet that they are 'linked from'. In other words,
group all the related sheets in the initial workbook (use Ctrl^Clicks to
select each), and copy/paste them into a new workbook all at once. This way
the formulas in the copied sheets should no longer be linked to the original
workbook, but to the copies that were made.

HTH,

TK

 

[dicko1]

Seems that when I try any type of Cut/Paste or Cut/Paste Special it
still copies the reference.

Thanks,
R

I think the easiest solution would be to copy the sheets that are 'linked to'
at the same time as the sheet that they are 'linked from'. In other words,
group all the related sheets in the initial workbook (use Ctrl^Clicks to
select each), and copy/paste them into a new workbook all at once. This way
the formulas in the copied sheets should no longer be linked to the original
workbook, but to the copies that were made.

HTH,

TK

I am creating a copy of a worksheet into a new workbook and all of the
formulas automatically reference the old workbook.

For example:

Formula in Old Workbook is

=R9*L17+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

Once I create a copy of the worksheet which contains this formula and
others like it into a new workbook, the formula automatically changes
too

=R9*L17+GETPIVOTDATA("Billing Amount",'[oldworkbook.xls]Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

I just want the same formula to be copied to the new workbook, not pull
from the old workbook.

Please help.
Ryan

 

[Gord Dibben]

Another thought.

Right-click on the sheet tab and "move or copy"

Check mark in "create a copy" and pick New Book from the "to book" dialog.


Gord

Seems that when I try any type of Cut/Paste or Cut/Paste Special it
still copies the reference.

Thanks,
R

I think the easiest solution would be to copy the sheets that are 'linked to'
at the same time as the sheet that they are 'linked from'. In other words,
group all the related sheets in the initial workbook (use Ctrl^Clicks to
select each), and copy/paste them into a new workbook all at once. This way
the formulas in the copied sheets should no longer be linked to the original
workbook, but to the copies that were made.

HTH,

TK

I am creating a copy of a worksheet into a new workbook and all of the
formulas automatically reference the old workbook.

For example:

Formula in Old Workbook is

=R9*L17+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

Once I create a copy of the worksheet which contains this formula and
others like it into a new workbook, the formula automatically changes
too

=R9*L17+GETPIVOTDATA("Billing Amount",'[oldworkbook.xls]Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

I just want the same formula to be copied to the new workbook, not pull
from the old workbook.

Please help.
Ryan

 

[Gord Dibben]

Nope...........forget that one too.

The edit>replace seems the best way to go.


Gord

Another thought.

Right-click on the sheet tab and "move or copy"

Check mark in "create a copy" and pick New Book from the "to book" dialog.


Gord

Seems that when I try any type of Cut/Paste or Cut/Paste Special it
still copies the reference.

Thanks,
R

I think the easiest solution would be to copy the sheets that are 'linked to'
at the same time as the sheet that they are 'linked from'. In other words,
group all the related sheets in the initial workbook (use Ctrl^Clicks to
select each), and copy/paste them into a new workbook all at once. This way
the formulas in the copied sheets should no longer be linked to the original
workbook, but to the copies that were made.

HTH,

TK

:

I am creating a copy of a worksheet into a new workbook and all of the
formulas automatically reference the old workbook.

For example:

Formula in Old Workbook is

=R9*L17+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

Once I create a copy of the worksheet which contains this formula and
others like it into a new workbook, the formula automatically changes
too

=R9*L17+GETPIVOTDATA("Billing Amount",'[oldworkbook.xls]Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

I just want the same formula to be copied to the new workbook, not pull
from the old workbook.

Please help.
Ryan

Gord Dibben MS Excel MVP

 

[dicko1]

Yeah, seems that the edit>replace is the only way to do it.
Thanks for the tip.
Ryan

Nope...........forget that one too.

The edit>replace seems the best way to go.


Gord

Another thought.

Right-click on the sheet tab and "move or copy"

Check mark in "create a copy" and pick New Book from the "to book" dialog.


Gord

Seems that when I try any type of Cut/Paste or Cut/Paste Special it
still copies the reference.

Thanks,
R

T Kirtley wrote:
I think the easiest solution would be to copy the sheets that are 'linked to'
at the same time as the sheet that they are 'linked from'. In other words,
group all the related sheets in the initial workbook (use Ctrl^Clicks to
select each), and copy/paste them into a new workbook all at once. This way
the formulas in the copied sheets should no longer be linked to the original
workbook, but to the copies that were made.

HTH,

TK

:

I am creating a copy of a worksheet into a new workbook and all of the
formulas automatically reference the old workbook.

For example:

Formula in Old Workbook is

=R9*L17+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

Once I create a copy of the worksheet which contains this formula and
others like it into a new workbook, the formula automatically changes
too

=R9*L17+GETPIVOTDATA("Billing Amount",'[oldworkbook.xls]Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

I just want the same formula to be copied to the new workbook, not pull
from the old workbook.

Please help.
Ryan

Gord Dibben MS Excel MVP

 

[Guest]

Sorry for not replying sooner, but my approach does work; I do it all the time.

To copy the sheets all at once you must select them as a group before
copying them. You can then copy them by dragging the grouped sheets with the
right mouse button pressed into the new workbook. If this is done then the
copied sheets will reference each other, not the source workbook.

TK

Seems that when I try any type of Cut/Paste or Cut/Paste Special it
still copies the reference.

Thanks,
R

I think the easiest solution would be to copy the sheets that are 'linked to'
at the same time as the sheet that they are 'linked from'. In other words,
group all the related sheets in the initial workbook (use Ctrl^Clicks to
select each), and copy/paste them into a new workbook all at once. This way
the formulas in the copied sheets should no longer be linked to the original
workbook, but to the copies that were made.

HTH,

TK

I am creating a copy of a worksheet into a new workbook and all of the
formulas automatically reference the old workbook.

For example:

Formula in Old Workbook is

=R9*L17+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

Once I create a copy of the worksheet which contains this formula and
others like it into a new workbook, the formula automatically changes
too

=R9*L17+GETPIVOTDATA("Billing Amount",'[oldworkbook.xls]Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

I just want the same formula to be copied to the new workbook, not pull
from the old workbook.

Please help.
Ryan

 

[Pete_UK]

You can use Edit | Replace (or CTRL-H) after highlighting all cells in
the sheet in the new workbook to remove the references to the old file
directly, i.e.:

Find What: [oldworkbook.xls]
Replace with: leave blank

then click Replace All.

You will need both files to be open, otherwise the reference to
[oldworkbook.xls] will expand to include the full path.

Hope this helps.

Pete

Yeah, seems that the edit>replace is the only way to do it.
Thanks for the tip.
Ryan

Nope...........forget that one too.

The edit>replace seems the best way to go.


Gord

Another thought.

Right-click on the sheet tab and "move or copy"

Check mark in "create a copy" and pick New Book from the "to book" dialog.


Gord

On 23 Aug 2006 13:10:43 -0700, (e-mail address removed) wrote:

Seems that when I try any type of Cut/Paste or Cut/Paste Special it
still copies the reference.

Thanks,
R

T Kirtley wrote:
I think the easiest solution would be to copy the sheets that are 'linked to'
at the same time as the sheet that they are 'linked from'. In other words,
group all the related sheets in the initial workbook (use Ctrl^Clicks to
select each), and copy/paste them into a new workbook all at once. This way
the formulas in the copied sheets should no longer be linked to the original
workbook, but to the copies that were made.

HTH,

TK

:

I am creating a copy of a worksheet into a new workbook and all of the
formulas automatically reference the old workbook.

For example:

Formula in Old Workbook is

=R9*L17+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

Once I create a copy of the worksheet which contains this formula and
others like it into a new workbook, the formula automatically changes
too

=R9*L17+GETPIVOTDATA("Billing Amount",'[oldworkbook.xls]Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","TradeCode","F04")

I just want the same formula to be copied to the new workbook, not pull
from the old workbook.

Please help.
Ryan

Gord Dibben MS Excel MVP