Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named formulas in CHOOSE need to be Relative references when paste | Excel Worksheet Functions | |||
copy/Paste references other worksheet | Excel Worksheet Functions | |||
How do I paste horizontal references in large array | Excel Worksheet Functions | |||
Copy/Paste without changing location references | Excel Discussion (Misc queries) | |||
How can I paste a formula that references a chart of data | Excel Worksheet Functions |