|
Question about Copy/Paste functions
I'm trying to copy and paste contents of a cell to another cell in order to
complete an entire column (about 300 rows). The contents of the cell is a function which acts on data on two separate worksheets. I want the copy/paste to update some of the arguments of the function (arguments that change with each row) but not other parts (arguments from the second worksheet that don't change) The problem is that everytime I paste the function, it wants to automatically update ALL the arguments of the function. What I'm trying to copy/paste: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) to then make rows like this: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G 2,AH!B4),PRODUCT(H2,AH!B5)) =SUM(PRODUCT(E3,AH!B2),PRODUCT(F3,AH!B3),PRODUCT(G 3,AH!B4),PRODUCT(H3,AH!B5)) =SUM(PRODUCT(E4,AH!B2),PRODUCT(F4,AH!B3),PRODUCT(G 4,AH!B4),PRODUCT(H4,AH!B5)) =SUM(PRODUCT(E5,AH!B2),PRODUCT(F5,AH!B3),PRODUCT(G 5,AH!B4),PRODUCT(H5,AH!B5)) .... etc ... What I'm getting is: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G 2,AH!B4),PRODUCT(H2,AH!B5)) =SUM(PRODUCT(E3,AH!B3),PRODUCT(F3,AH!B4),PRODUCT(G 3,AH!B5),PRODUCT(H3,AH!B6)) =SUM(PRODUCT(E4,AH!B4),PRODUCT(F4,AH!B5),PRODUCT(G 4,AH!B6),PRODUCT(H4,AH!B7)) =SUM(PRODUCT(E5,AH!B5),PRODUCT(F5,AH!B6),PRODUCT(G 5,AH!B7),PRODUCT(H5,AH!B8)) .... etc ... As you can see in the first example; I only want to update the first argument in each PRODUCT(X,Y)... but what I'm getting is both being updated which doesn't work for what I'm doing. I've tried copying and pasting cell by cell, copying and pasting multiple sells, using Edit-Fill ..., and Paste Special - and I can't seem to figure this out other than to manually enter this for every single cell (which is prohibitively too labour intensive because what I'm actually trying to do is much more complicated than this example) If this makes any sense, I'd appreciate your help - Thanks! Kevin |
Question about Copy/Paste functions
In the formula that you want to copy, insert a dollar sign before the row numbers and/or column letters that you want to remain constant before copying and pasting -- CaptainQuattro ------------------------------------------------------------------------ CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763 View this thread: http://www.excelforum.com/showthread...hreadid=546182 |
Question about Copy/Paste functions
If I catch your drift you want the B2, B3, B5 parts of the formula to remain
the same while copying down? If so use the absolute formula for those cells in the original formula. (As in B2 should be $B$2 in the formula, B3 should be $B$3, etc.) When you copy down the the 1st argument will progress down but the second arguements will remain $B$2, $B$2 etc "Kevin" wrote: I'm trying to copy and paste contents of a cell to another cell in order to complete an entire column (about 300 rows). The contents of the cell is a function which acts on data on two separate worksheets. I want the copy/paste to update some of the arguments of the function (arguments that change with each row) but not other parts (arguments from the second worksheet that don't change) The problem is that everytime I paste the function, it wants to automatically update ALL the arguments of the function. What I'm trying to copy/paste: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) to then make rows like this: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G 2,AH!B4),PRODUCT(H2,AH!B5)) =SUM(PRODUCT(E3,AH!B2),PRODUCT(F3,AH!B3),PRODUCT(G 3,AH!B4),PRODUCT(H3,AH!B5)) =SUM(PRODUCT(E4,AH!B2),PRODUCT(F4,AH!B3),PRODUCT(G 4,AH!B4),PRODUCT(H4,AH!B5)) =SUM(PRODUCT(E5,AH!B2),PRODUCT(F5,AH!B3),PRODUCT(G 5,AH!B4),PRODUCT(H5,AH!B5)) ... etc ... What I'm getting is: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G 2,AH!B4),PRODUCT(H2,AH!B5)) =SUM(PRODUCT(E3,AH!B3),PRODUCT(F3,AH!B4),PRODUCT(G 3,AH!B5),PRODUCT(H3,AH!B6)) =SUM(PRODUCT(E4,AH!B4),PRODUCT(F4,AH!B5),PRODUCT(G 4,AH!B6),PRODUCT(H4,AH!B7)) =SUM(PRODUCT(E5,AH!B5),PRODUCT(F5,AH!B6),PRODUCT(G 5,AH!B7),PRODUCT(H5,AH!B8)) ... etc ... As you can see in the first example; I only want to update the first argument in each PRODUCT(X,Y)... but what I'm getting is both being updated which doesn't work for what I'm doing. I've tried copying and pasting cell by cell, copying and pasting multiple sells, using Edit-Fill ..., and Paste Special - and I can't seem to figure this out other than to manually enter this for every single cell (which is prohibitively too labour intensive because what I'm actually trying to do is much more complicated than this example) If this makes any sense, I'd appreciate your help - Thanks! Kevin |
Question about Copy/Paste functions
=SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!$B$3),PRODUCT (G1,AH!$B$4),PRODUCT(H1,AH!$B$5))
"Kevin" skrev: I'm trying to copy and paste contents of a cell to another cell in order to complete an entire column (about 300 rows). The contents of the cell is a function which acts on data on two separate worksheets. I want the copy/paste to update some of the arguments of the function (arguments that change with each row) but not other parts (arguments from the second worksheet that don't change) The problem is that everytime I paste the function, it wants to automatically update ALL the arguments of the function. What I'm trying to copy/paste: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) to then make rows like this: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G 2,AH!B4),PRODUCT(H2,AH!B5)) =SUM(PRODUCT(E3,AH!B2),PRODUCT(F3,AH!B3),PRODUCT(G 3,AH!B4),PRODUCT(H3,AH!B5)) =SUM(PRODUCT(E4,AH!B2),PRODUCT(F4,AH!B3),PRODUCT(G 4,AH!B4),PRODUCT(H4,AH!B5)) =SUM(PRODUCT(E5,AH!B2),PRODUCT(F5,AH!B3),PRODUCT(G 5,AH!B4),PRODUCT(H5,AH!B5)) ... etc ... What I'm getting is: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G 2,AH!B4),PRODUCT(H2,AH!B5)) =SUM(PRODUCT(E3,AH!B3),PRODUCT(F3,AH!B4),PRODUCT(G 3,AH!B5),PRODUCT(H3,AH!B6)) =SUM(PRODUCT(E4,AH!B4),PRODUCT(F4,AH!B5),PRODUCT(G 4,AH!B6),PRODUCT(H4,AH!B7)) =SUM(PRODUCT(E5,AH!B5),PRODUCT(F5,AH!B6),PRODUCT(G 5,AH!B7),PRODUCT(H5,AH!B8)) ... etc ... As you can see in the first example; I only want to update the first argument in each PRODUCT(X,Y)... but what I'm getting is both being updated which doesn't work for what I'm doing. I've tried copying and pasting cell by cell, copying and pasting multiple sells, using Edit-Fill ..., and Paste Special - and I can't seem to figure this out other than to manually enter this for every single cell (which is prohibitively too labour intensive because what I'm actually trying to do is much more complicated than this example) If this makes any sense, I'd appreciate your help - Thanks! Kevin |
Question about Copy/Paste functions
ahh perfect - thanks!
"tim m" wrote: If I catch your drift you want the B2, B3, B5 parts of the formula to remain the same while copying down? If so use the absolute formula for those cells in the original formula. (As in B2 should be $B$2 in the formula, B3 should be $B$3, etc.) When you copy down the the 1st argument will progress down but the second arguements will remain $B$2, $B$2 etc "Kevin" wrote: I'm trying to copy and paste contents of a cell to another cell in order to complete an entire column (about 300 rows). The contents of the cell is a function which acts on data on two separate worksheets. I want the copy/paste to update some of the arguments of the function (arguments that change with each row) but not other parts (arguments from the second worksheet that don't change) The problem is that everytime I paste the function, it wants to automatically update ALL the arguments of the function. What I'm trying to copy/paste: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) to then make rows like this: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G 2,AH!B4),PRODUCT(H2,AH!B5)) =SUM(PRODUCT(E3,AH!B2),PRODUCT(F3,AH!B3),PRODUCT(G 3,AH!B4),PRODUCT(H3,AH!B5)) =SUM(PRODUCT(E4,AH!B2),PRODUCT(F4,AH!B3),PRODUCT(G 4,AH!B4),PRODUCT(H4,AH!B5)) =SUM(PRODUCT(E5,AH!B2),PRODUCT(F5,AH!B3),PRODUCT(G 5,AH!B4),PRODUCT(H5,AH!B5)) ... etc ... What I'm getting is: =SUM(PRODUCT(E1,AH!B2),PRODUCT(F1,AH!B3),PRODUCT(G 1,AH!B4),PRODUCT(H1,AH!B5)) =SUM(PRODUCT(E2,AH!B2),PRODUCT(F2,AH!B3),PRODUCT(G 2,AH!B4),PRODUCT(H2,AH!B5)) =SUM(PRODUCT(E3,AH!B3),PRODUCT(F3,AH!B4),PRODUCT(G 3,AH!B5),PRODUCT(H3,AH!B6)) =SUM(PRODUCT(E4,AH!B4),PRODUCT(F4,AH!B5),PRODUCT(G 4,AH!B6),PRODUCT(H4,AH!B7)) =SUM(PRODUCT(E5,AH!B5),PRODUCT(F5,AH!B6),PRODUCT(G 5,AH!B7),PRODUCT(H5,AH!B8)) ... etc ... As you can see in the first example; I only want to update the first argument in each PRODUCT(X,Y)... but what I'm getting is both being updated which doesn't work for what I'm doing. I've tried copying and pasting cell by cell, copying and pasting multiple sells, using Edit-Fill ..., and Paste Special - and I can't seem to figure this out other than to manually enter this for every single cell (which is prohibitively too labour intensive because what I'm actually trying to do is much more complicated than this example) If this makes any sense, I'd appreciate your help - Thanks! Kevin |
| All times are GMT +1. The time now is 09:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com