Dynamic Formula Based on latest column added (Budget Vs actual)

[akash121]

Hello experts,

I need your help to know what formula should I apply to get dynamic results.
In the attached spreadsheet I have sample data of my monthly report, where we have monthly actual column and remaining month forecast (M05 to M12).
I also have monthly budget and monthly forecast formula to show the variance from current month M04 actual cost. every month I have to manually move the cell reference of actual column to show the variance when compared to latest month, cell reference for Budget variance and Forecast variance column are fixed,
For example for next month I have to move the formula manually from M04 to M05 to show the comparison against latest actual for all the rows manually.

please advise is there any formula which can do this movement every month once M05 gets updated with actual next month? to put formula in column O and P?

Thank you
Akash

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Actual	Actual	Actual	Actual	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Current Budget	Current Forecast	Variance vs Budget	Variance Vs Forecast
2	M01	M02	M03	M04	M05	M06	M07	M08	M09	M10	M11	M12	M04	M04	M04	M04
3	100	100	100	101	100	100	100	100	100	100	100	100	100	90	-1	-11
4	200	180	200	199	200	200	200	200	200	200	200	200	200	190	1	-9
5	250	220	250	275	250	250	250	250	250	250	250	250	250	270	-25	-5
Sheet1

 

[Toadstool]

Hi akash121,

Is this what you want?

Akash121.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Actual	Actual	Actual	Actual	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Current Budget	Current Forecast	Variance vs Budget	Variance Vs Forecast
2	M01	M02	M03	M04	M05	M06	M07	M08	M09	M10	M11	M12	M04	M04	M04	M04
3	100	100	100	101	100	100	100	100	100	100	100	100	100	90	-1	-11
4	200	180	200	199	200	200	200	200	200	200	200	200	200	190	1	-9
5	250	220	250	275	250	250	250	250	250	250	250	250	250	270	-25	-5
Sheet2

Cell Formulas
Range	Formula
O3:P5	O3	=M3-INDEX($A3:$L3,AGGREGATE(14,6,COLUMN($A$3:$L$3)/($A$1:$L$1="Actual"),1))

 

[akash121]

Hi akash121,

Is this what you want?

Akash121.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Actual	Actual	Actual	Actual	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Current Budget	Current Forecast	Variance vs Budget	Variance Vs Forecast
2	M01	M02	M03	M04	M05	M06	M07	M08	M09	M10	M11	M12	M04	M04	M04	M04
3	100	100	100	101	100	100	100	100	100	100	100	100	100	90	-1	-11
4	200	180	200	199	200	200	200	200	200	200	200	200	200	190	1	-9
5	250	220	250	275	250	250	250	250	250	250	250	250	250	270	-25	-5
Sheet2

Cell Formulas
Range	Formula
O3:P5	O3	=M3-INDEX($A3:$L3,AGGREGATE(14,6,COLUMN($A$3:$L$3)/($A$1:$L$1="Actual"),1))

Thank you so much sir, you are awesome

 

[Toadstool]

You're welcome!
Thanks for the feedback.