Dynamic MTD

[anum]

Hi if I were to want the month only based on this selection, what's the best way please? as the if statement one is too long and I'm sure there's an easier way!
I would like cols C, D & E pick up values from second table based on PY, BP etc.

3697 P&L_FY21 Jan YTD.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Jan
3		MTD ANALYSIS
4	EUR (000)	FY20	FBP21	ACT21	vs BP21	vs PY
5
6	Net Trade Sales	0	0	0	-	-
7
8	Currency	0	0	0	-	-
9	Cost of Other Revenue	0	0	0	-	-
10	COGS	0	0	0	-	-
11		6%	#DIV/0!	#DIV/0!
12
13	IC Sales & Services	0	0	0	-	-
14	Standard GP	0	0	0	-	-
15
16
17
18
19	Formula from here for each cycle	PY
20		YTD	Year	1	2	3	4	5	6	7	8	9	10	11	12
21		2020	2020	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
22	Net Trade Sales	100	7,800.00	100.00	200.00	300.00	400.00	500.00	600.00	700.00	800.00	900.00	1,000.00	1,100.00	1,200.00
23	Currency	100	3,600.00	100.00	0.00	300.00	0.00	500.00	0.00	700.00	0.00	900.00	0.00	1,100.00	0.00
24	Cost of Other Revenue	0	4,200.00	0.00	200.00	0.00	400.00	0.00	600.00	0.00	800.00	0.00	1,000.00	0.00	1,200.00
25	IC Sales & Services	100	7,550.00	100.00	200.00	250.00	400.00	450.00	600.00	650.00	800.00	850.00	1,000.00	1,050.00	1,200.00
Sheet2

Cell Formulas
Range	Formula
B2	B2	=Selections!C3
C3	C3	=UPPER(Selections!A1)&" MTD ANALYSIS"
C6:E6,C13:E13,C8:E9	C6	=M6
F6,F13:F14,F8:F10	F6	=E6-D6
G6,G13:G14,G8:G10	G6	=E6-C6
C10:E10	C10	=SUM(C7:C9)
D11:E11	D11	=D10/D6
C14:E14	C14	=C6-C10+C13
C22:C25	C22	=SUM(E22:INDEX(E22:P22,Selections!$C$4))
D22:D25	D22	=SUM(E22:P22)

 

[Toadstool]

Hi Anum,

You didn't provide the Selections sheet, I don't know how your FY runs so I assumed Jan to Dec, I don't know what you mean by PY, BP so I'm supplying what I think you want for column C.

Anum.xlsx
	B	C
2	May
3		MAY MTD ANALYSIS
4	EUR (000)	FY20
5
6	Net Trade Sales	1500
7
8	Currency	900
9	Cost of Other Revenue	600
10	COGS	1500
11		0.061598
12
13	IC Sales & Services	1400
14	Standard GP	1400
Sheet2

Cell Formulas
Range	Formula
C3	C3	=UPPER(Selections!A1)&" MTD ANALYSIS"
C6,C13,C8:C9	C6	=SUMPRODUCT(--($E$22:$P$25)*($E$20:$P$20<=INT(SEARCH($B$2,"Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec")/4)+1)*($B$22:$B$25=B6))
C10	C10	=SUM(C7:C9)
C14	C14	=C6-C10+C13

 

[anum]

Hi Anum,

You didn't provide the Selections sheet, I don't know how your FY runs so I assumed Jan to Dec, I don't know what you mean by PY, BP so I'm supplying what I think you want for column C.

Anum.xlsx
	B	C
2	May
3		MAY MTD ANALYSIS
4	EUR (000)	FY20
5
6	Net Trade Sales	1500
7
8	Currency	900
9	Cost of Other Revenue	600
10	COGS	1500
11		0.061598
12
13	IC Sales & Services	1400
14	Standard GP	1400
Sheet2

Cell Formulas
Range	Formula
C3	C3	=UPPER(Selections!A1)&" MTD ANALYSIS"
C6,C13,C8:C9	C6	=SUMPRODUCT(--($E$22:$P$25)*($E$20:$P$20<=INT(SEARCH($B$2,"Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec")/4)+1)*($B$22:$B$25=B6))
C10	C10	=SUM(C7:C9)
C14	C14	=C6-C10+C13

Ah amazing!! you're a genius - thank you ever so much