This issue comes from an annual project finance tracker that should update Actuals & Forecast totals month-by-month.
Every month I need to:
Confirm my Actuals (money spent in the month just ended)
Update my Forecasts (money that I think will come out in the months ahead)
In my table I have:
A column with the Actuals total
A column with the Forecast total
Twelve columns, one for each month (when the month ends I overwrite what was a forecast figure with the actual).
At the moment I am updating the column references in the formula manually but have 10 tables per project and currently 8 projects! Which is 160 updates to make as I have to increment the actual total column and decrement the forecast total column each month. I've mocked this up in the attached file.
To save myself so many manual and error-prone updates I would like to control it from a single drop-down menu (also a named range). In longhand this would say:
I have selected the current month to be April from the drop-down list. Therefore calculate the Actual total as columns January to April in the current row, and calculate the Forecast total from the May to December columns in the current row.
Any help to get this nailed will be absolutely smashing!
Bookmarks