Paste Formulas without new References
 

Each week I get a production report for the previous week. Each differently
dated report will have the same type of information on the tabs named exactly
the same.

I insert a Results page and have formulas for numerous fields that reference
the other two worksheets.

Is it possible to Copy my Results worksheet from the previous week's file to
the new week WITHOUT it updating the references (and without having to copy
each individual cell? When I do that now, it changes all my formulas to
reference the previous file. I want the EXACT formula transferred.

For Example:

File 1 --
A5: =SUM(G1:G8)
A6: =SUM(G9:G15)
A7: =SUM(G16:G24)
A8: =SUM(G25:G27)

when I highlight A5:A8 and paste it into file 2 it looks like this

File 2 --
A5: =SUM('[File 1]'!G1:G8)
A6: =SUM('[File 1]'!G9:G15)
A7: =SUM('[File 1]'!G16:G24)
A8: =SUM('[File 1]'!G25:G27)

I want the " '[File 1]'! " to stay off the new file since G1:G27 will
contain the new totals for a different week's production numbers. Currently,
I have to delete that info from every single cell that has a formula.

Paste Formulas without new References
 

Hi,

You don't need to delete the '[File 1]'! indi vidually you can do it bt find
and replace.
put '[File 1]'! in the find box and leave replace box empty.

Thanks,
--
Farhad Hodjat


"sweens319" wrote:

Each week I get a production report for the previous week. Each differently
dated report will have the same type of information on the tabs named exactly
the same.

I insert a Results page and have formulas for numerous fields that reference
the other two worksheets.

Is it possible to Copy my Results worksheet from the previous week's file to
the new week WITHOUT it updating the references (and without having to copy
each individual cell? When I do that now, it changes all my formulas to
reference the previous file. I want the EXACT formula transferred.

For Example:

File 1 --
A5: =SUM(G1:G8)
A6: =SUM(G9:G15)
A7: =SUM(G16:G24)
A8: =SUM(G25:G27)

when I highlight A5:A8 and paste it into file 2 it looks like this

File 2 --
A5: =SUM('[File 1]'!G1:G8)
A6: =SUM('[File 1]'!G9:G15)
A7: =SUM('[File 1]'!G16:G24)
A8: =SUM('[File 1]'!G25:G27)

I want the " '[File 1]'! " to stay off the new file since G1:G27 will
contain the new totals for a different week's production numbers. Currently,
I have to delete that info from every single cell that has a formula.

Paste Formulas without new References
 

One method.

Select cells to copy then EditReplace

what: =

with: ^^^

Replace all.

Paste to other file new worksheet then reverse the process on both.


Gord Dibben MS Excel MVP

On Fri, 28 Sep 2007 12:02:02 -0700, sweens319
wrote:

Each week I get a production report for the previous week. Each differently
dated report will have the same type of information on the tabs named exactly
the same.

I insert a Results page and have formulas for numerous fields that reference
the other two worksheets.

Is it possible to Copy my Results worksheet from the previous week's file to
the new week WITHOUT it updating the references (and without having to copy
each individual cell? When I do that now, it changes all my formulas to
reference the previous file. I want the EXACT formula transferred.

For Example:

File 1 --
A5: =SUM(G1:G8)
A6: =SUM(G9:G15)
A7: =SUM(G16:G24)
A8: =SUM(G25:G27)

when I highlight A5:A8 and paste it into file 2 it looks like this

File 2 --
A5: =SUM('[File 1]'!G1:G8)
A6: =SUM('[File 1]'!G9:G15)
A7: =SUM('[File 1]'!G16:G24)
A8: =SUM('[File 1]'!G25:G27)

I want the " '[File 1]'! " to stay off the new file since G1:G27 will
contain the new totals for a different week's production numbers. Currently,
I have to delete that info from every single cell that has a formula.

Paste Formulas without new References
 

Thank you...I don't know why I didn't think of that.
Actually, I wasn't sure if it would work since the cells will have a value
in them. I thought maybe it would search the value rather than the formula.

"Farhad" wrote:

Hi,

You don't need to delete the '[File 1]'! indi vidually you can do it bt find
and replace.
put '[File 1]'! in the find box and leave replace box empty.

Thanks,
--
Farhad Hodjat


"sweens319" wrote:

Each week I get a production report for the previous week. Each differently
dated report will have the same type of information on the tabs named exactly
the same.

I insert a Results page and have formulas for numerous fields that reference
the other two worksheets.

Is it possible to Copy my Results worksheet from the previous week's file to
the new week WITHOUT it updating the references (and without having to copy
each individual cell? When I do that now, it changes all my formulas to
reference the previous file. I want the EXACT formula transferred.

For Example:

File 1 --
A5: =SUM(G1:G8)
A6: =SUM(G9:G15)
A7: =SUM(G16:G24)
A8: =SUM(G25:G27)

when I highlight A5:A8 and paste it into file 2 it looks like this

File 2 --
A5: =SUM('[File 1]'!G1:G8)
A6: =SUM('[File 1]'!G9:G15)
A7: =SUM('[File 1]'!G16:G24)
A8: =SUM('[File 1]'!G25:G27)

I want the " '[File 1]'! " to stay off the new file since G1:G27 will
contain the new totals for a different week's production numbers. Currently,
I have to delete that info from every single cell that has a formula.

Paste Formulas without new References
 

I guess I'm missing something.

In Exel 2007, when I put your formulas in a new workbook in A5:A8 like

A5: =SUM(G1:G8)
A6: =SUM(G9:G15)
A7: =SUM(G16:G24)
A8: =SUM(G25:G27)

and I copy those cells and paste them into another new workbook or simply
copy the whole worksheet to another new workbook, I get:

A5: =SUM(G1:G8)
A6: =SUM(G9:G15)
A7: =SUM(G16:G24)
A8: =SUM(G25:G27)

You say that your formulas reference other worksheets, but I don't see the
references to other worksheets in the formulas - e.g. =SUM(G1:G8) refers to
the worksheet the formula is on.

"sweens319" wrote in message
...
Each week I get a production report for the previous week. Each
differently
dated report will have the same type of information on the tabs named
exactly
the same.

I insert a Results page and have formulas for numerous fields that
reference
the other two worksheets.

Is it possible to Copy my Results worksheet from the previous week's file
to
the new week WITHOUT it updating the references (and without having to
copy
each individual cell? When I do that now, it changes all my formulas to
reference the previous file. I want the EXACT formula transferred.

For Example:

File 1 --
A5: =SUM(G1:G8)
A6: =SUM(G9:G15)
A7: =SUM(G16:G24)
A8: =SUM(G25:G27)

when I highlight A5:A8 and paste it into file 2 it looks like this

File 2 --
A5: =SUM('[File 1]'!G1:G8)
A6: =SUM('[File 1]'!G9:G15)
A7: =SUM('[File 1]'!G16:G24)
A8: =SUM('[File 1]'!G25:G27)

I want the " '[File 1]'! " to stay off the new file since G1:G27 will
contain the new totals for a different week's production numbers.
Currently,
I have to delete that info from every single cell that has a formula.