Updating a summary sheet using VBA

[srea]

I have a workbook that contains several sheets that we use for forecasting and actuals tracking. One of the sheets is a monthly summary sheet; there is also a sheet for actuals and another for forecasting. Both the forecasting and actuals sheets are divided into months, i.e, Jan thru Dec and they are updated on a monthly basis. For example: this months actuals sheet would show the total spent on a project during following month and also months proceeding. The forecast sheet shows everything forecast to be spent for the several months in advance.

What I curently have is a simple formula that references the sum of the cell or cells in each respective sheet. The summary sheet totals both the actuals and the forecast and shows the total expected spend. The problem is that once a actual is posted for the month then the Forecast sumamry should no longer include the prior months forecast for that month. Example 10k is forecast for the month, but the 10k is now an actual and no longer a forecast so the formula has to be manually changed to exclude the amount that was forecast for that month. It looks like =SUM(Forecast!A2:L2), but now should be =SUM(Forecast!A2:k2), so lasts months forecast is no longer included in the Forecast. Is there a way to automate this using VBA or another formula?

 

[repairman615]

Perhaps this may work, you may have to adapt this formula a bit to suit your needs.

=SUM(Forecast!A2:CHOOSE(MONTH(TODAY()),A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2))

Welcome to the MREXCEL msg board!!