How do I copy a formula with the cell increasing by increments? in Excel General Questions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Jaxboo 10/9/2007 12:20 PM PST
  Question
  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.
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Niek Otten 10/9/2007 12:38 PM PST
   
  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" <Jaxboo@discussions.microsoft.com> wrote in message news:B33EA3E1-01B9-4C53-9D07-581A883C2ED0@microsoft.com...
|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.


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Niek Otten 10/9/2007 1:26 PM PST
   
  Sorry, misread your question!

Fortunately you got some better answers

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" <nicolaus@xs4all.nl> wrote in message news:%23pPyIuqCIHA.1212@TK2MSFTNGP05.phx.gbl...
| 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" <Jaxboo@discussions.microsoft.com> wrote in message news:B33EA3E1-01B9-4C53-9D07-581A883C2ED0@microsoft.com...
||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.
|
|


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Elkar  10/9/2007 12:39 PM PST
  Answer
  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.
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
RagDyer 10/9/2007 12:52 PM PST
  Answer
  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" <Elkar@discussions.microsoft.com> wrote in message
news:BFCA31E3-E1BA-46C4-9842-B01C29B0CC68@microsoft.com...
> 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.


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
dgb82 8/7/2008 10:22 AM PST
   
  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" <Elkar@discussions.microsoft.com> wrote in message
> news:BFCA31E3-E1BA-46C4-9842-B01C29B0CC68@microsoft.com...
> > 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.
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Jaxboo 10/9/2007 1:32 PM PST
   
  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.
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Elkar  10/9/2007 1:43 PM PST
  Answer
  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.
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Gord Dibben 10/9/2007 1:20 PM PST
   
  =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 <Jaxboo@discussions.microsoft.com>
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.

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Nick W 1/6/2009 4:27 PM PST
   
  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.
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Ë­ÊÇË­ 1/6/2009 5:32 PM PST
   
  Do you mean:
sum($B$5:B11)
sum($B$12:B18)
sum($B$19:B25)

$!
"Nick W" <Nick W@discussions.microsoft.com> дÈëÏûÏ¢ÐÂÎÅ:2A984B79-0DC6-4835-A1E0-5433731D251F@microsoft.com...
>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.


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
T. Valko 1/6/2009 7:20 PM PST
   
  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 W@discussions.microsoft.com> wrote in message
news:2A984B79-0DC6-4835-A1E0-5433731D251F@microsoft.com...
>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.


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Nick W 1/7/2009 5:59 AM PST
   
  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))
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
David Biddulph 1/7/2009 10:09 AM PST
   
  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" <NickW@discussions.microsoft.com> wrote in message
news:59FFBF8F-7344-4DBD-BAE1-D10EB35A5D9B@microsoft.com...
>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))


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
David Biddulph 1/7/2009 10:50 AM PST
   
  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
news:4964ea98_1@glkas0286.greenlnk.net...
> 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" <NickW@discussions.microsoft.com> wrote in message
> news:59FFBF8F-7344-4DBD-BAE1-D10EB35A5D9B@microsoft.com...
>>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))
>
>


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies  
  More...