PDA

View Full Version : relative range / Offset within a sum - how to change the range summed



SunshineMel
04-11-2016, 01:14 PM
I have a range of cells that needs to be summed every month, and this range changes based on the month.
For example, if I am working on March, the remainder of the year, April through December will be summed. Next month (April) I will sum May through December.
This formula change currently needs to be made manually each month, and several times throughout the workbook.
This repetitive manual process is what I am trying to eliminate.

Could I use Offset, or possibly a relative range to get this to happen?
What would be the cleanest?

Currently my sum for this example is in cell O8, summing the cells in the row to the left of it.
Below is what I am playing with, but for obvious reasons, it isn't working.

Does anyone have other ideas or suggestions?

Range("O8").Select
ActiveCell.FormulaR1C1 = "=SUM(columnOffset=1):N8"

Bob Phillips
04-11-2016, 01:59 PM
With Range("O8")

.Formula = "=SUM(" & .Offset(0, Month(Date) - 12).Address(False, False) & ":N8)"
End With

SunshineMel
04-11-2016, 02:30 PM
That works well, but would there be a solution that would refer to the month/date within a cell at the top of the sheet, or something more variable, so that the code will work even if I am updating the workbook outside of the time frame I usually do?
I will likely be building a "choose" formula into the workbook to take care of month titles on the various pages.