Paste Formulas without new References

[sweens319]

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.

 

[Farhad]

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,

 

[Gord Dibben]

One method.

Select cells to copy then Edit>Replace

what: =

with: ^^^

Replace all.

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


Gord Dibben MS Excel MVP

 

[sweens319]

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.

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

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.

 

[Wondering]

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.