Calculate Year-to-date figures taken from another table in one cell based on selecting a month in a dropdown list

[Potbellyyemi]

I want cells to update "Actual" and "Budget" year-to-date values taken from a separate table based on my selecting the month from the dropdown list in Cell A1. For example, when I select Jan-23, figures for Jan-23 will show. When I select Feb-23, a cumulative figure for Jan 23 - Feb-23 will show, and so on and so forth, all the way to Dec-23. Is there a formula that can do this?

A formula for "Current Month" was ok as I could just do a match on the description and on the month selected in the dropdown, but I can't figure out what to do for Year-to-date as it needs to pick up (or ignore) additional columns when I select a month . The formula needs to be in the first table below from cells G4 - G18 and the figures are taken from the 2nd table below.

Many thanks in advance for your help!

Management accounts working file - Jan 2023.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Jan-23
2		CURRENT MONTH					YEAR TO DATE
3	DESCRIPTION	Actual	Budget	Variance	% Var		Actual	Budget	Variance	% Var
4	Turnover	36	34	1	4.0%				-	-
5	Cost of sales	7	12	(5)	(39.1%)				-	-
6	Gross Profit	29	23	6	26.1%				-	-
7	GPM	0	0	0	21.3%				-	-
8	Advertising	-	2	(2)	(100.0%)				-	-
9	People costs	65	174	(109)	(62.6%)				-	-
10	Professional fees/IP	0	9	(9)	(99.4%)				-	-
11	Property costs	-	6	(6)	(100.0%)				-	-
12	Other overheads	1	17	(17)	(96.7%)				-	-
13	Total overheads	66	211	(145)	(68.8%)				-	-
14	EBITDA	(37)	(188)	151	(80.3%)				-	-
15	R&D claim*	6	6	(0)	(0.7%)				-	-
16	PAT	(32)	(183)	151	(82.7%)				-	-
17	Gross burn**	66	211	(145)	(68.8%)				-	-
18	Net burn**	37	188	(151)	(80.3%)				-	-
19
P&L Summary Table

Cell Formulas
Range	Formula
B4:B18	B4	=OFFSET('Slide tables'!$P$4,MATCH('P&L Summary Table'!$A4,'Slide tables'!$P$5:$P$23,0),MATCH('P&L Summary Table'!$A$1,'Slide tables'!$Q$4:$AB$4,0))
C4:C18	C4	=OFFSET('Slide tables (Base)'!$P$4,MATCH('P&L Summary Table'!$A4,'Slide tables (Base)'!$P$5:$P$23,0),MATCH('P&L Summary Table'!$A$1,'Slide tables (Base)'!$Q$4:$AB$4,0))
D4:D18,I4:I18	D4	=B4-C4
E4:E18,J4:J18	E4	=IF(C4=0,"-",(D4/C4))

Cells with Data Validation
Cell	Allow	Criteria
A1	List	Jan-2023,Feb-2023,Mar-2023,Apr-2023,May-2023,Jun-2023,Jul-2023,Aug-2023,Sep-2023,Oct-2023,Nov-2023,Dec-2023

Management accounts working file - Jan 2023.xlsx
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
4	£000	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Total
5	Turnover	34	87	88	95	104	105	113	132	135	145	160	167	1,364
6	Cost of sales	12	13	13	15	15	15	18	19	19	22	23	13	196
7	Gross Profit	23	75	75	80	89	90	95	113	115	123	136	154	1,168
8	GPM	66%	85%	85%	85%	86%	86%	84%	86%	86%	85%	85%	92%	86%
9	Advertising	2	4	4	5	5	5	6	7	7	7	8	8	68
10	People costs	174	190	205	206	207	207	208	210	210	211	212	212	2,454
11	BD travel	3	17	16	16	11	11	11	11	11	11	11	11	143
12	Professional fees/IP	9	47	45	9	9	9	9	9	9	9	9	9	181
13	Property costs	6	6	6	6	6	6	6	6	6	6	6	6	67
14	Other overheads	17	17	17	17	17	17	17	17	17	17	17	17	207
15	Total overheads	211	281	294	259	256	256	257	260	260	261	263	263	3,120
16
17	EBITDA	(188)	(207)	(219)	(179)	(166)	(166)	(162)	(147)	(145)	(138)	(126)	(109)	(1,952)
18	Depn	0	0	0	0	0	0	0	0	0	0	0	0	4
19	PBT	(189)	(207)	(219)	(180)	(167)	(166)	(162)	(147)	(145)	(139)	(127)	(109)	(1,956)
20	R&D claim*	6	6	6	6	6	6	6	6	6	6	6	6	67
21	PAT	(183)	(202)	(214)	(174)	(161)	(160)	(157)	(141)	(139)	(133)	(121)	(103)	(1,889)
22	Gross burn	211	281	294	259	256	256	257	260	260	261	263	263	3,120
23	Net burn	188	207	219	179	166	166	162	147	145	138	126	109	1,952
Slide tables (Base)

 

[Alex Blakenburg]

Try this in G4, the inconsistent use of "*" may come back to bite you later.
I have removed the "**" using substitute to make this work.

=SUM(
         FILTER(
                     FILTER('Slide tables (Base)'!$P$4:$AC$23,
                                  ('Slide tables (Base)'!$P$4:$AC$4>=DATE(2023,1,1)) * ('Slide tables (Base)'!$P$4:$AC$4<=$A$1)),
                    'Slide tables (Base)'!$P$4:$P$23=SUBSTITUTE($A4,"**","")),0)

 

[Potbellyyemi]

Try this in G4, the inconsistent use of "*" may come back to bite you later.
I have removed the "**" using substitute to make this work.

=SUM(
         FILTER(
                     FILTER('Slide tables (Base)'!$P$4:$AC$23,
                                  ('Slide tables (Base)'!$P$4:$AC$4>=DATE(2023,1,1)) * ('Slide tables (Base)'!$P$4:$AC$4<=$A$1)),
                    'Slide tables (Base)'!$P$4:$P$23=SUBSTITUTE($A4,"**","")),0)

@Alex Blakenburg Thanks a lot for this, it worked. I have now removed the use of "**" in the workbook. How do I modify the formula correctly by deleting the need to substitute? everything I have tried is coming back with a #VALUE! error.

Thank you.

 

[Alex Blakenburg]

Without the substitute and return 0 is nothing is found use this:

=SUM(
         FILTER(
                     FILTER('Slide tables (Base)'!$P$4:$AC$23,
                                  ('Slide tables (Base)'!$P$4:$AC$4>=DATE(2023,1,1)) * ('Slide tables (Base)'!$P$4:$AC$4<=$A$1)),
                    'Slide tables (Base)'!$P$4:$P$23=$A4,0))

Unless you have some error checking eg make sure the vertical additions agree to the totals you are brining in, you might be better off letting the error show if it is not found. Which is what would happen if you left out the ",0" at the end

=SUM(
         FILTER(
                     FILTER('Slide tables (Base)'!$P$4:$AC$23,
                                  ('Slide tables (Base)'!$P$4:$AC$4>=DATE(2023,1,1)) * ('Slide tables (Base)'!$P$4:$AC$4<=$A$1)),
                    'Slide tables (Base)'!$P$4:$P$23=$A4))