How to set up worksheet where cells change monthly
 

I have an excel spreadsheet where each month the cell location changes - i.e.
SUM(C56:C79) = January is changed to SUM(C57:C79) in February. Each month it
increments down. I have to repeat this formula 120 times, with each one a
different amount. Is there any way to code this into Excel so that I don't
need to change each cell every month?

How to set up worksheet where cells change monthly
 

Maybe you could use a SUMIF function instead, using date as the
criteria, but that depends on what exactly you are trying to do.

Hope this helps.

Pete

On Nov 10, 5:11*pm, Robina wrote:
I have an excel spreadsheet where each month the cell location changes - i.e.
*SUM(C56:C79) = January is changed to SUM(C57:C79) in February. Each month it
increments down. I have to repeat this formula 120 times, with each one a
different *amount. Is there any way to code this into Excel so that I don't
need to change each cell every month?

How to set up worksheet where cells change monthly
 

Without seeing, try

SUM($C56:$C$79)

Copy down. Notice the $ EXCEPT in front of the 56

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robina" wrote in message
...
I have an excel spreadsheet where each month the cell location changes -
i.e.
SUM(C56:C79) = January is changed to SUM(C57:C79) in February. Each month
it
increments down. I have to repeat this formula 120 times, with each one a
different amount. Is there any way to code this into Excel so that I
don't
need to change each cell every month?

How to set up worksheet where cells change monthly
 

Hi,

=SUM(INDIRECT("C"&A1+56&":C79"))

In this case you would increase the value in cell A1 by 1 each month and the
formula would increment. I noted that you only incremented the first
reference, if you wanted to do both

=SUM(INDIRECT("C"&A1+56&":C"&A1+79))

Once you have modified all the formulas it will be a snap to update.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Robina" wrote:

I have an excel spreadsheet where each month the cell location changes - i.e.
SUM(C56:C79) = January is changed to SUM(C57:C79) in February. Each month it
increments down. I have to repeat this formula 120 times, with each one a
different amount. Is there any way to code this into Excel so that I don't
need to change each cell every month?

How to set up worksheet where cells change monthly
 

I need to remove the monthly total, not increment. for example in February I
no longer need January's information.

"ShaneDevenshire" wrote:

Hi,

=SUM(INDIRECT("C"&A1+56&":C79"))

In this case you would increase the value in cell A1 by 1 each month and the
formula would increment. I noted that you only incremented the first
reference, if you wanted to do both

=SUM(INDIRECT("C"&A1+56&":C"&A1+79))

Once you have modified all the formulas it will be a snap to update.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Robina" wrote:

I have an excel spreadsheet where each month the cell location changes - i.e.
SUM(C56:C79) = January is changed to SUM(C57:C79) in February. Each month it
increments down. I have to repeat this formula 120 times, with each one a
different amount. Is there any way to code this into Excel so that I don't
need to change each cell every month?

How to set up worksheet where cells change monthly
 

Try this idea
=SUM($c$1:OFFSET($c$56,MONTH(TODAY())-1,0))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Robina" wrote in message
...
I need to remove the monthly total, not increment. for example in February
I
no longer need January's information.

"ShaneDevenshire" wrote:

Hi,

=SUM(INDIRECT("C"&A1+56&":C79"))

In this case you would increase the value in cell A1 by 1 each month and
the
formula would increment. I noted that you only incremented the first
reference, if you wanted to do both

=SUM(INDIRECT("C"&A1+56&":C"&A1+79))

Once you have modified all the formulas it will be a snap to update.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Robina" wrote:

I have an excel spreadsheet where each month the cell location
changes - i.e.
SUM(C56:C79) = January is changed to SUM(C57:C79) in February. Each
month it
increments down. I have to repeat this formula 120 times, with each one
a
different amount. Is there any way to code this into Excel so that I
don't
need to change each cell every month?