How to produce a dynamic calculation in a sheet?

[basils57]

Hi Folks

I currently use a standard monthly profit & loss report which gives the monthly totals and cumulative totals as the year progresses. (sample attached)
The monthly variance (AF3) is simply E3-F3 for December and G3-H3 for January etc.
I would like to know if there is a way these totals can be automatically produced monthly as the various numbers are inputted into the sheet, rather than having to manually enter the changed formulas?

Thanks in advance for any suggestions.

 

[vletm]

basils57
Your sample files has all sample data in cell C3
... it should have much more sample data with needed formulas and variations.
Do it need to have that kind of layout?

 

[basils57]

Hi
The attachment was just a rough indication of where the calculation is required (column AF): Column C holds about 40 different cost centres descending down the page, together with the Budget figures; the Actual details are entered monthly, and various totals are summarised further down below this info. Obviously, the Month Variance changes monthly.
The layout needs to be like this for historical reasons.

 

[GraH - Guido]

Assuming today() can be used as a reference date, try
[AF3]=INDEX(E3:AB3,MATCH(EOMONTH(TODAY(),-1)+1,$E$1:$AB$1,0))-INDEX(E3:AB3,MATCH(EOMONTH(TODAY(),-1)+1,$E$1:$AB$1,0)+1)

Where you see today() in the formula, it can be replaced by a cell reference containing the date. Then you might even get rid of the EOMONTH construction if that reference date is the first of a possible month.