Hi,
I have a workbook which I updated every month with a new row of data. There are rolling trends calculated which need to be updated as well.
In January, the trend formula is as follows : =(SUM(E15:E26)/SUM(D15:D26))/(SUM(E3:E14)/SUM(D3:D14))
The next month, the formulas are shifted down one row. For example in February, the formula reads:
=(SUM(E16:E27)/SUM(D16:D27))/(SUM(E4:E15)/SUM(D4:D15))
The formulas move down one row every month in this manner until January where the formulas are reset to the first formula.
I have been updating the formulas by dragging down the reference boxes for the four ranges, but there are 5 trends calculated on each sheet, with 8 sheets in the entire workbook. The process can get tedious. The locations of the formulas and the ranges also differ from sheet to sheet, so they can't be grouped. I came up with a long sum offset formula to achieve the same result, but was told the formulas need to stay the same in the work book. Is there any way to automate this process with a VBA code?
Any help or direction would be greatly appreciated!
<tbody>
</tbody>
I have a workbook which I updated every month with a new row of data. There are rolling trends calculated which need to be updated as well.
In January, the trend formula is as follows : =(SUM(E15:E26)/SUM(D15:D26))/(SUM(E3:E14)/SUM(D3:D14))
The next month, the formulas are shifted down one row. For example in February, the formula reads:
=(SUM(E16:E27)/SUM(D16:D27))/(SUM(E4:E15)/SUM(D4:D15))
The formulas move down one row every month in this manner until January where the formulas are reset to the first formula.
I have been updating the formulas by dragging down the reference boxes for the four ranges, but there are 5 trends calculated on each sheet, with 8 sheets in the entire workbook. The process can get tedious. The locations of the formulas and the ranges also differ from sheet to sheet, so they can't be grouped. I came up with a long sum offset formula to achieve the same result, but was told the formulas need to stay the same in the work book. Is there any way to automate this process with a VBA code?
Any help or direction would be greatly appreciated!
<tbody>
</tbody>
