Dynamic Variance formula as reporting month changes

[Zakky]

Hi, All. I have produced a simple example of my monthly reporting file which i update manually for the relevant month by changing the formula in cells B13, C13, D13. I manually change the budget column reference 'E' (E3), say, for April each month. So for May i will change to 'F' (F3) so it picks up May budget. Is there a way to make the formula dynamic (without VBA) so it jumps to the month (column) in cell 'A9' (Relevant month) including previous months e.g Jan. Thank you for your help.

 

[kevin9999]

I don't know where your values in B11:D11 come from, but perhaps something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Product A	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000	13000
4	Product B	4000	6000	8000	10000	12000	14000	16000	18000	20000	22000	24000	26000
5	Product C	1000	1500	2000	2500	3000	3500	4000	4500	5000	5500	6000	6500
6
7
8
9	Apr
10		Product A	Product B	Product C
11	Units	1000	2000	3000
12
13	Variance	-4000	-8000	500
Sheet1

Cell Formulas
Range	Formula
B13:D13	B13	=B11-SUMPRODUCT(($B$2:$M$2=$A$9)*($A$3:$A$5=B10),$B$3:$M$5)

Cells with Data Validation
Cell	Allow	Criteria
A9	List	=$B$2:$M$2

 

[Zakky]

Kevin999, thank you for the prompt reply. Your solution works like a charm. Many thanks for saving me a lot of time each month.

 

[kevin9999]

Happy to help, and thanks for the feedback