Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Monthly Change Using Nested IFs? | Excel Worksheet Functions | |||
how can i change the colum width for cells on the same worksheet | Excel Discussion (Misc queries) | |||
How do I change the IRR formula for MONTHLY periodic payments? | Excel Worksheet Functions | |||
Change the font case of all cells in the worksheet to uppercase? | Excel Worksheet Functions | |||
calculate monthly average percentage of change | Excel Worksheet Functions |