|
How do I copy a formula with the cell increasing by increments?
I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into
the next cell in an increment so the next cell (below) will read sum(b7:b12) How would I do this? I have a large number of cells I will be doing this with and really do not want to have to go back and forth between worksheets to select the cells. |
How do I copy a formula with the cell increasing by increments?
Just drag the cell down. Place your cursor on the little square in the right bottom corner of the cell, press and hold the left
mouse button, move the mouse to the last destination cell (still keeping the mouse button pressed) and once you're there, release it. You can also copy and paste via the menu; Excel will adjust the cell references automatically. -- Kind regards, Niek Otten Microsoft MVP - Excel "Jaxboo" wrote in message ... |I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into | the next cell in an increment so the next cell (below) will read sum(b7:b12) | | How would I do this? I have a large number of cells I will be doing this | with and really do not want to have to go back and forth between worksheets | to select the cells. |
How do I copy a formula with the cell increasing by increments?
You can use the INDIRECT and ROW functions to do this. For example, assuming
your first formula will be placed in Row 1, you'd use: =SUM(INDIRECT("B"&ROW()*6-5&":B"&ROW()*6)) HTH, Elkar "Jaxboo" wrote: I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into the next cell in an increment so the next cell (below) will read sum(b7:b12) How would I do this? I have a large number of cells I will be doing this with and really do not want to have to go back and forth between worksheets to select the cells. |
How do I copy a formula with the cell increasing by increments?
A non-volatile approach:
=SUM(INDEX(B:B,6*ROWS($1:1)-5):INDEX(B:B,6*ROWS($1:1))) And copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Elkar" wrote in message ... You can use the INDIRECT and ROW functions to do this. For example, assuming your first formula will be placed in Row 1, you'd use: =SUM(INDIRECT("B"&ROW()*6-5&":B"&ROW()*6)) HTH, Elkar "Jaxboo" wrote: I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into the next cell in an increment so the next cell (below) will read sum(b7:b12) How would I do this? I have a large number of cells I will be doing this with and really do not want to have to go back and forth between worksheets to select the cells. |
How do I copy a formula with the cell increasing by increments?
=SUM(INDEX(B:B,ROW(B1)*6-5):INDEX(B:B,ROW(B1)*6))
Enter in C1 and drag/copy down until you get zeros. Gord Dibben MS Excel MVP On Tue, 9 Oct 2007 12:21:01 -0700, Jaxboo wrote: I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into the next cell in an increment so the next cell (below) will read sum(b7:b12) How would I do this? I have a large number of cells I will be doing this with and really do not want to have to go back and forth between worksheets to select the cells. |
How do I copy a formula with the cell increasing by increments?
Sorry, misread your question!
Fortunately you got some better answers -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... | Just drag the cell down. Place your cursor on the little square in the right bottom corner of the cell, press and hold the left | mouse button, move the mouse to the last destination cell (still keeping the mouse button pressed) and once you're there, release | it. | | You can also copy and paste via the menu; Excel will adjust the cell references automatically. | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Jaxboo" wrote in message ... ||I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into || the next cell in an increment so the next cell (below) will read sum(b7:b12) || || How would I do this? I have a large number of cells I will be doing this || with and really do not want to have to go back and forth between worksheets || to select the cells. | | |
How do I copy a formula with the cell increasing by increments
How would the indirect function be changed to reflect data on a different
spreadsheet? "Elkar" wrote: You can use the INDIRECT and ROW functions to do this. For example, assuming your first formula will be placed in Row 1, you'd use: =SUM(INDIRECT("B"&ROW()*6-5&":B"&ROW()*6)) HTH, Elkar "Jaxboo" wrote: I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into the next cell in an increment so the next cell (below) will read sum(b7:b12) How would I do this? I have a large number of cells I will be doing this with and really do not want to have to go back and forth between worksheets to select the cells. |
How do I copy a formula with the cell increasing by increments
For a different sheet? You'd just add the sheet name inside of the INDIRECT
function, like this: =SUM(INDIRECT("'Sheet Name'!B"&ROW()*6-5&":B"&ROW()*6)) However, if you're asking about a different workbook, then that may become a problem. The INDIRECT function only works on different workbooks if they are open. In this case, I'd suggest using RagDyer's or Gord Dibben's solutions instead. HTH, Elkar "Jaxboo" wrote: How would the indirect function be changed to reflect data on a different spreadsheet? "Elkar" wrote: You can use the INDIRECT and ROW functions to do this. For example, assuming your first formula will be placed in Row 1, you'd use: =SUM(INDIRECT("B"&ROW()*6-5&":B"&ROW()*6)) HTH, Elkar "Jaxboo" wrote: I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into the next cell in an increment so the next cell (below) will read sum(b7:b12) How would I do this? I have a large number of cells I will be doing this with and really do not want to have to go back and forth between worksheets to select the cells. |
How do I copy a formula with the cell increasing by increments
In regards to what Jaxboo wrote below:
To start RagDyer, you were correct on Jaxboo question, but now I have more difficult problem. I would like to take a simple Sum formula such as Sum(b1:e6) and copy it into the next cell in an increment so the next cell (below) will read sum(b7:e12) How would you answer the above? "RagDyer" wrote: A non-volatile approach: =SUM(INDEX(B:B,6*ROWS($1:1)-5):INDEX(B:B,6*ROWS($1:1))) And copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Elkar" wrote in message ... You can use the INDIRECT and ROW functions to do this. For example, assuming your first formula will be placed in Row 1, you'd use: =SUM(INDIRECT("B"&ROW()*6-5&":B"&ROW()*6)) HTH, Elkar "Jaxboo" wrote: I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into the next cell in an increment so the next cell (below) will read sum(b7:b12) How would I do this? I have a large number of cells I will be doing this with and really do not want to have to go back and forth between worksheets to select the cells. |
How do I copy a formula with the cell increasing by increments?
I have a very similar situation to this but knowing very little about excel
functions I am hoping someone can help me understand the changes I need to make to get it to work. Working with 2 sheets (sheet1 and sheet2 to make it easier) Second sheet references the first, formula starts in B2 of sheet2 Formula needs to get the SUM of B5:B11 when copied down it needs to then get the SUM of B12:18 then B19:B25, etc I tried to manipulate the functions listed above, but they always seem to pick odd cells to start from, or pick weird increments to go up by. Any help would be greatly appreciated!! Thanks in advance. |
How do I copy a formula with the cell increasing by increments?
Assume you want the results to appear in D5 downwards.
Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) -- Biff Microsoft Excel MVP "Nick W" <Nick wrote in message ... I have a very similar situation to this but knowing very little about excel functions I am hoping someone can help me understand the changes I need to make to get it to work. Working with 2 sheets (sheet1 and sheet2 to make it easier) Second sheet references the first, formula starts in B2 of sheet2 Formula needs to get the SUM of B5:B11 when copied down it needs to then get the SUM of B12:18 then B19:B25, etc I tried to manipulate the functions listed above, but they always seem to pick odd cells to start from, or pick weird increments to go up by. Any help would be greatly appreciated!! Thanks in advance. |
How do I copy a formula with the cell increasing by increments
I should have been a little more specific, sorry. The formula is starting in
B2 of sheet 2. So I want it to be Sum('sheet1"!B5:B11) when it is copied down to B6 in Sheet 2, it will give the SUM('sheet1'!B12:B18). I appologize, my syntax is terrible because I really haven't been in a position that required me to use Excel for more than standard data entry purposes. I thought that grouping sums would be pretty straight forward. So far from what I have learned just trying to get this to work, I am amazed at the things I can do with this program. "T. Valko" wrote: Assume you want the results to appear in D5 downwards. Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) |
How do I copy a formula with the cell increasing by increments
If you want the input cells to shift down 4 rows when you copy your formula
down 4 rows, just change T Valko's =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) to =SUM(OFFSET(Sheet1!B$2,(ROWS(D$2:D2)-1)*7/4,,7)) -- David Biddulph "Nick W" wrote in message ... I should have been a little more specific, sorry. The formula is starting in B2 of sheet 2. So I want it to be Sum('sheet1"!B5:B11) when it is copied down to B6 in Sheet 2, it will give the SUM('sheet1'!B12:B18). I appologize, my syntax is terrible because I really haven't been in a position that required me to use Excel for more than standard data entry purposes. I thought that grouping sums would be pretty straight forward. So far from what I have learned just trying to get this to work, I am amazed at the things I can do with this program. "T. Valko" wrote: Assume you want the results to appear in D5 downwards. Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) |
How do I copy a formula with the cell increasing by increments
Enter this formula in B2:
=SUM(Sheet1!B5:B11) Enter this formula in B6: =SUM(Sheet1!B12:B18) Select the range B2:B9 and drag copy down as needed. -- Biff Microsoft Excel MVP "Nick W" wrote in message ... I should have been a little more specific, sorry. The formula is starting in B2 of sheet 2. So I want it to be Sum('sheet1"!B5:B11) when it is copied down to B6 in Sheet 2, it will give the SUM('sheet1'!B12:B18). I appologize, my syntax is terrible because I really haven't been in a position that required me to use Excel for more than standard data entry purposes. I thought that grouping sums would be pretty straight forward. So far from what I have learned just trying to get this to work, I am amazed at the things I can do with this program. "T. Valko" wrote: Assume you want the results to appear in D5 downwards. Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) |
How do I copy a formula with the cell increasing by increments
Should, of course, have been
=SUM(OFFSET(Sheet1!B$5,(ROWS(D$2:D2)-1)*7/4,,7)) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... If you want the input cells to shift down 4 rows when you copy your formula down 4 rows, just change T Valko's =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) to =SUM(OFFSET(Sheet1!B$2,(ROWS(D$2:D2)-1)*7/4,,7)) -- David Biddulph "Nick W" wrote in message ... I should have been a little more specific, sorry. The formula is starting in B2 of sheet 2. So I want it to be Sum('sheet1"!B5:B11) when it is copied down to B6 in Sheet 2, it will give the SUM('sheet1'!B12:B18). I appologize, my syntax is terrible because I really haven't been in a position that required me to use Excel for more than standard data entry purposes. I thought that grouping sums would be pretty straight forward. So far from what I have learned just trying to get this to work, I am amazed at the things I can do with this program. "T. Valko" wrote: Assume you want the results to appear in D5 downwards. Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) |
How do I copy a formula with the cell increasing by increments
Didn't work for me.
B10 shows =SUM(Sheet1!B13:B19) B14 shows =SUM(Sheet1!B20:B26) B18 shows =SUM(Sheet1!B21:B27) so alternately stepping 1 row & 7 rows. I think your original OFFSET formula was the right way, modified for the OP's changed requirements. -- David Biddulph "T. Valko" wrote in message ... Enter this formula in B2: =SUM(Sheet1!B5:B11) Enter this formula in B6: =SUM(Sheet1!B12:B18) Select the range B2:B9 and drag copy down as needed. -- Biff Microsoft Excel MVP "Nick W" wrote in message ... I should have been a little more specific, sorry. The formula is starting in B2 of sheet 2. So I want it to be Sum('sheet1"!B5:B11) when it is copied down to B6 in Sheet 2, it will give the SUM('sheet1'!B12:B18). I appologize, my syntax is terrible because I really haven't been in a position that required me to use Excel for more than standard data entry purposes. I thought that grouping sums would be pretty straight forward. So far from what I have learned just trying to get this to work, I am amazed at the things I can do with this program. "T. Valko" wrote: Assume you want the results to appear in D5 downwards. Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) |
How do I copy a formula with the cell increasing by increments
Works for me (Excel 2002). I get:
B2: =SUM(Sheet1!B5:B11) B6: =SUM(Sheet1!B12:B18) B10: =SUM(Sheet1!B13:B19) B14: =SUM(Sheet1!B20:B26) B18: =SUM(Sheet1!B21:B27) And all cells in-between are empty. -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Didn't work for me. B10 shows =SUM(Sheet1!B13:B19) B14 shows =SUM(Sheet1!B20:B26) B18 shows =SUM(Sheet1!B21:B27) so alternately stepping 1 row & 7 rows. I think your original OFFSET formula was the right way, modified for the OP's changed requirements. -- David Biddulph "T. Valko" wrote in message ... Enter this formula in B2: =SUM(Sheet1!B5:B11) Enter this formula in B6: =SUM(Sheet1!B12:B18) Select the range B2:B9 and drag copy down as needed. -- Biff Microsoft Excel MVP "Nick W" wrote in message ... I should have been a little more specific, sorry. The formula is starting in B2 of sheet 2. So I want it to be Sum('sheet1"!B5:B11) when it is copied down to B6 in Sheet 2, it will give the SUM('sheet1'!B12:B18). I appologize, my syntax is terrible because I really haven't been in a position that required me to use Excel for more than standard data entry purposes. I thought that grouping sums would be pretty straight forward. So far from what I have learned just trying to get this to work, I am amazed at the things I can do with this program. "T. Valko" wrote: Assume you want the results to appear in D5 downwards. Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) |
How do I copy a formula with the cell increasing by increments
Well duh!
Disregard that previous post. It's dinner time! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Works for me (Excel 2002). I get: B2: =SUM(Sheet1!B5:B11) B6: =SUM(Sheet1!B12:B18) B10: =SUM(Sheet1!B13:B19) B14: =SUM(Sheet1!B20:B26) B18: =SUM(Sheet1!B21:B27) And all cells in-between are empty. -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Didn't work for me. B10 shows =SUM(Sheet1!B13:B19) B14 shows =SUM(Sheet1!B20:B26) B18 shows =SUM(Sheet1!B21:B27) so alternately stepping 1 row & 7 rows. I think your original OFFSET formula was the right way, modified for the OP's changed requirements. -- David Biddulph "T. Valko" wrote in message ... Enter this formula in B2: =SUM(Sheet1!B5:B11) Enter this formula in B6: =SUM(Sheet1!B12:B18) Select the range B2:B9 and drag copy down as needed. -- Biff Microsoft Excel MVP "Nick W" wrote in message ... I should have been a little more specific, sorry. The formula is starting in B2 of sheet 2. So I want it to be Sum('sheet1"!B5:B11) when it is copied down to B6 in Sheet 2, it will give the SUM('sheet1'!B12:B18). I appologize, my syntax is terrible because I really haven't been in a position that required me to use Excel for more than standard data entry purposes. I thought that grouping sums would be pretty straight forward. So far from what I have learned just trying to get this to work, I am amazed at the things I can do with this program. "T. Valko" wrote: Assume you want the results to appear in D5 downwards. Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) |
How do I copy a formula with the cell increasing by increments
Yes, it's been a long year. We're all getting tired! :-)
-- David Biddulph T. Valko wrote: Well duh! Disregard that previous post. It's dinner time! "T. Valko" wrote in message ... Works for me (Excel 2002). I get: B2: =SUM(Sheet1!B5:B11) B6: =SUM(Sheet1!B12:B18) B10: =SUM(Sheet1!B13:B19) B14: =SUM(Sheet1!B20:B26) B18: =SUM(Sheet1!B21:B27) And all cells in-between are empty. -- Biff Microsoft Excel MVP "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Didn't work for me. B10 shows =SUM(Sheet1!B13:B19) B14 shows =SUM(Sheet1!B20:B26) B18 shows =SUM(Sheet1!B21:B27) so alternately stepping 1 row & 7 rows. I think your original OFFSET formula was the right way, modified for the OP's changed requirements. -- David Biddulph "T. Valko" wrote in message ... Enter this formula in B2: =SUM(Sheet1!B5:B11) Enter this formula in B6: =SUM(Sheet1!B12:B18) Select the range B2:B9 and drag copy down as needed. -- Biff Microsoft Excel MVP "Nick W" wrote in message ... I should have been a little more specific, sorry. The formula is starting in B2 of sheet 2. So I want it to be Sum('sheet1"!B5:B11) when it is copied down to B6 in Sheet 2, it will give the SUM('sheet1'!B12:B18). I appologize, my syntax is terrible because I really haven't been in a position that required me to use Excel for more than standard data entry purposes. I thought that grouping sums would be pretty straight forward. So far from what I have learned just trying to get this to work, I am amazed at the things I can do with this program. "T. Valko" wrote: Assume you want the results to appear in D5 downwards. Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) |
How do I copy a formula with the cell increasing by increments
The offset funtion worked perfectly. Thank you so much! I had to do a
little tweaking on a few of the cells, but once I understood how the variables worked it was a snap. Thank you everyone, this will make future work a breeze! "T. Valko" wrote: Assume you want the results to appear in D5 downwards. Enter this formula in D5 and copy down as needed: =SUM(OFFSET(B$5,(ROWS(D$5:D5)-1)*7,,7)) -- Biff Microsoft Excel MVP "Nick W" <Nick wrote in message ... I have a very similar situation to this but knowing very little about excel functions I am hoping someone can help me understand the changes I need to make to get it to work. Working with 2 sheets (sheet1 and sheet2 to make it easier) Second sheet references the first, formula starts in B2 of sheet2 Formula needs to get the SUM of B5:B11 when copied down it needs to then get the SUM of B12:18 then B19:B25, etc I tried to manipulate the functions listed above, but they always seem to pick odd cells to start from, or pick weird increments to go up by. Any help would be greatly appreciated!! Thanks in advance. |
How do I copy a formula with the cell increasing by increments
I have a question related to this topic also, how would you use this with the
function sumif & countif. These are the formulas I'm currently using, SUMIF(H2:H73,"0",F2:F73)& COUNTIF(G2:G73,"0"). "Nick W" wrote: I have a very similar situation to this but knowing very little about excel functions I am hoping someone can help me understand the changes I need to make to get it to work. Working with 2 sheets (sheet1 and sheet2 to make it easier) Second sheet references the first, formula starts in B2 of sheet2 Formula needs to get the SUM of B5:B11 when copied down it needs to then get the SUM of B12:18 then B19:B25, etc I tried to manipulate the functions listed above, but they always seem to pick odd cells to start from, or pick weird increments to go up by. Any help would be greatly appreciated!! Thanks in advance. |
How do I copy a formula with the cell increasing by increments?
I have a similar issue but I am just trying to reference cells and not sum.
I have a row with several sets of data in in it that I want to consolidate into a more vertical structure for analysis. for example Row 1, columns A:AX, the first two columns stay the same and then in sets of 6 columns I want to bring the data down for a new total of 8 rows: A-B will remain the same for the following 8 rows 1. C1:H1 2. I1:N1 3. O1:T1 4: U1:Z1 5: AA1:AF1 6: AG1:AL1 7. AM1:AR1 8. AS1:AX1 I need to reapeat this this for rows 2-68 and likely more. While the time I have spent trying to figure this out I could have copied and posted the whole set. I am trying to find a fast, unbiased way to reduce human error. Thanks in advance! e "Jaxboo" wrote: I would like to take a simple Sum formula such as Sum(b1:b6) and copy it into the next cell in an increment so the next cell (below) will read sum(b7:b12) How would I do this? I have a large number of cells I will be doing this with and really do not want to have to go back and forth between worksheets to select the cells. |
| All times are GMT +1. The time now is 02:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com