Home |
Search |
Today's Posts |
#1
|
|||
|
|||
need to copy/paste formula w/o it changing
Hi there! Using E02 on XP. Not a programmer but love
learning better ways to do stuff. I have data being generated from an ooooold Lotus 1-2-3 file that can be pasted into Excel as values. There are 5 reports. Need about 100 cells from each for a big one page summary report. (Someone has been typing it each month!) So, I have created a file with the summary report on sheet 1 and the 5 Lotus reports are pasted to sheets 2-6. And now I'm experiencing the joyous pain of typing a few thousand formulas to link the cells. If I try to copy/paste the formulas to speed things up, Excel keeps using the wrong logic to 'change' the cell reference. Problem is, my links on sheet 1 are linear and my data on sheets 2-6 is running columnar. (Transposing the report data is not an option.) So, what I want is: =P4!R26 =P4!R27 =P4!R28 =P4!S26 =P4!S27 =P4!S28 When I try a copy/paste, I get something like this: =P4!S27 =P4!T27 =P4!U27 Is there a way to paste a formula EXACTLY as you copied it? Then I could just edit the letters as I go. Also, once I get sheet2 linked to sheet1 is there a way to copy all the links to sheets 3,4,etc.? Just looking for a quick overview of tips and shortcuts to best use my brain rather than frying it with monotonous typing. Thanks in advance for any help or advice!!! |
#2
|
|||
|
|||
"Bonnie" wrote in message ... Hi there! Using E02 on XP. Not a programmer but love learning better ways to do stuff. I have data being generated from an ooooold Lotus 1-2-3 file that can be pasted into Excel as values. There are 5 reports. Need about 100 cells from each for a big one page summary report. (Someone has been typing it each month!) So, I have created a file with the summary report on sheet 1 and the 5 Lotus reports are pasted to sheets 2-6. And now I'm experiencing the joyous pain of typing a few thousand formulas to link the cells. If I try to copy/paste the formulas to speed things up, Excel keeps using the wrong logic to 'change' the cell reference. Problem is, my links on sheet 1 are linear and my data on sheets 2-6 is running columnar. (Transposing the report data is not an option.) So, what I want is: =P4!R26 =P4!R27 =P4!R28 =P4!S26 =P4!S27 =P4!S28 When I try a copy/paste, I get something like this: =P4!S27 =P4!T27 =P4!U27 Is there a way to paste a formula EXACTLY as you copied it? Then I could just edit the letters as I go. Also, once I get sheet2 linked to sheet1 is there a way to copy all the links to sheets 3,4,etc.? Just looking for a quick overview of tips and shortcuts to best use my brain rather than frying it with monotonous typing. Thanks in advance for any help or advice!!! Instead of =P4!R26, try =$P$4!$R$26 /Fredrik |
#3
|
|||
|
|||
Okaaaaaay, and the dollar signs do what? Why only wrap the
P and not the 4 on the sheet name? I'll play with it a bit...Thanks for the bone. I'll take what I can get. -----Original Message----- "Bonnie" wrote in message ... Hi there! Using E02 on XP. Not a programmer but love learning better ways to do stuff. I have data being generated from an ooooold Lotus 1-2-3 file that can be pasted into Excel as values. There are 5 reports. Need about 100 cells from each for a big one page summary report. (Someone has been typing it each month!) So, I have created a file with the summary report on sheet 1 and the 5 Lotus reports are pasted to sheets 2-6. And now I'm experiencing the joyous pain of typing a few thousand formulas to link the cells. If I try to copy/paste the formulas to speed things up, Excel keeps using the wrong logic to 'change' the cell reference. Problem is, my links on sheet 1 are linear and my data on sheets 2-6 is running columnar. (Transposing the report data is not an option.) So, what I want is: =P4!R26 =P4!R27 =P4!R28 =P4!S26 =P4!S27 =P4!S28 When I try a copy/paste, I get something like this: =P4!S27 =P4!T27 =P4!U27 Is there a way to paste a formula EXACTLY as you copied it? Then I could just edit the letters as I go. Also, once I get sheet2 linked to sheet1 is there a way to copy all the links to sheets 3,4,etc.? Just looking for a quick overview of tips and shortcuts to best use my brain rather than frying it with monotonous typing. Thanks in advance for any help or advice!!! Instead of =P4!R26, try =$P$4!$R$26 /Fredrik . |
#4
|
|||
|
|||
On Thu, 10 Mar 2005 06:21:48 -0800, "Bonnie"
wrote: Hi there! Using E02 on XP. Not a programmer but love learning better ways to do stuff. I have data being generated from an ooooold Lotus 1-2-3 file that can be pasted into Excel as values. There are 5 reports. Need about 100 cells from each for a big one page summary report. (Someone has been typing it each month!) So, I have created a file with the summary report on sheet 1 and the 5 Lotus reports are pasted to sheets 2-6. And now I'm experiencing the joyous pain of typing a few thousand formulas to link the cells. If I try to copy/paste the formulas to speed things up, Excel keeps using the wrong logic to 'change' the cell reference. Problem is, my links on sheet 1 are linear and my data on sheets 2-6 is running columnar. (Transposing the report data is not an option.) So, what I want is: =P4!R26 =P4!R27 =P4!R28 =P4!S26 =P4!S27 =P4!S28 When I try a copy/paste, I get something like this: =P4!S27 =P4!T27 =P4!U27 Is there a way to paste a formula EXACTLY as you copied it? Then I could just edit the letters as I go. Also, once I get sheet2 linked to sheet1 is there a way to copy all the links to sheets 3,4,etc.? Just looking for a quick overview of tips and shortcuts to best use my brain rather than frying it with monotonous typing. Thanks in advance for any help or advice!!! This is going to be messy, but it works: Select the cells with the formulas and perform a "find and replace" finding "=" and replacing it with a " =" (add a space). You now have a text string you can copy without changing cell references. Copy and paste your selection to its new location. Reverse the "find and replace" results replacing " =" with "=" (replace space= with =). Franks answer works well too. Read up on absolute cell references to see the power of the $ : ) Don S |
#5
|
|||
|
|||
Don, Don, Don! Thank you VERY much! You gave me exactly
what I need to go to my next nightmare; linking in 4 more sheets and only the worksheet name will change so your 'messy' fix is absolutely just what I needed. Frank's answer gave me what I needed to finish that first sheet of formulas and yup, those dollar signs lock it down. Thanks again for taking the time to reply!!! -----Original Message----- On Thu, 10 Mar 2005 06:21:48 -0800, "Bonnie" wrote: Hi there! Using E02 on XP. Not a programmer but love learning better ways to do stuff. I have data being generated from an ooooold Lotus 1-2-3 file that can be pasted into Excel as values. There are 5 reports. Need about 100 cells from each for a big one page summary report. (Someone has been typing it each month!) So, I have created a file with the summary report on sheet 1 and the 5 Lotus reports are pasted to sheets 2-6. And now I'm experiencing the joyous pain of typing a few thousand formulas to link the cells. If I try to copy/paste the formulas to speed things up, Excel keeps using the wrong logic to 'change' the cell reference. Problem is, my links on sheet 1 are linear and my data on sheets 2-6 is running columnar. (Transposing the report data is not an option.) So, what I want is: =P4!R26 =P4!R27 =P4!R28 =P4!S26 =P4!S27 =P4!S28 When I try a copy/paste, I get something like this: =P4!S27 =P4!T27 =P4!U27 Is there a way to paste a formula EXACTLY as you copied it? Then I could just edit the letters as I go. Also, once I get sheet2 linked to sheet1 is there a way to copy all the links to sheets 3,4,etc.? Just looking for a quick overview of tips and shortcuts to best use my brain rather than frying it with monotonous typing. Thanks in advance for any help or advice!!! This is going to be messy, but it works: Select the cells with the formulas and perform a "find and replace" finding "=" and replacing it with a " =" (add a space). You now have a text string you can copy without changing cell references. Copy and paste your selection to its new location. Reverse the "find and replace" results replacing " =" with "=" (replace space= with =). Franks answer works well too. Read up on absolute cell references to see the power of the $ : ) Don S . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A cell reference in a formula changing | Excel Worksheet Functions | |||
Copying a formula and changing ONE value... | Excel Worksheet Functions | |||
HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE | Excel Discussion (Misc queries) | |||
Changing the Color of a Word in Formula | Excel Worksheet Functions | |||
Can I use TODAY Function in formula without it changing the next . | Excel Discussion (Misc queries) |