Drop Down Capability question

[NinaE_11]

Hello,
I keep a table of quarter end market values with about 50 different data points. In the past, I've created a new workbook for every quarter, but I'd like to simplify it by keeping four calendar quarters in one worksheet, if possible. My idea is to somehow utilize a dropdown list in cell A1 so you can select Q1, Q2, Q3 and Q4 and then have the datapoints in my table update to reflect appropriate quarter end data. However, I'm not sure how to even attempt to start this. Would it require writing VBA code or macros? Would anybody have any advice on how to get started with this project?

Thank you!

 

[Toadstool]

Hi NinaE_11,

Yes, that sounds a better design. You could do almost anything with VBA but if you want to avoid it then you can use functions to retrieve matching data from another sheet, using SMALL function o AGGREGATE to only pull selected data.

Does your data contain the actual quarter number or just a date?
Why not use one sheet for all years as well as quarters?

If you can give an example of your data I'll dummy up a sheet.

 

[NinaE_11]

Hi NinaE_11,

Yes, that sounds a better design. You could do almost anything with VBA but if you want to avoid it then you can use functions to retrieve matching data from another sheet, using SMALL function o AGGREGATE to only pull selected data.

Does your data contain the actual quarter number or just a date?
Why not use one sheet for all years as well as quarters?

If you can give an example of your data I'll dummy up a sheet.

I would love to combine all years/quarters! Baby steps... Not opposed to using VBA, but maybe functions could get me started and I could work off of it from there? This is a sample of what I maintain each quarter. Ideally, I could utilize a blank cell and provide a dropdown list for user to select which quarter they would like to view, and have all these values reflect that ending date market value. I use various SUM, IF, SUMIFs formulas to pull from a master list of account names and values at each quarter end. Date is in 9/30/20 format. Is this helpful?

					Total Firm Assets (TFA)
						Small	SMID	Outside
						Cap	Cap	Composite	Totals
					Accts (#)	15	2	3	20
					Assets ($)	$629.7	$49.2	$8.7	$687.5
					% of AUM	91.6%	7.2%	1.3%	100.0%
		Tax Exempt		Taxable
		Small	SMID	Outside				Small	SMID	Outside
		Cap	Cap	Composite	Sub-Total	% of AUM		Cap	Cap	Composite	Sub-Total	% of AUM
	#	9	2	2	13			6	0	1	7
	$	$551.9	$49.2	$4.0	$605.1	88.0%		$77.7	$0.0	$4.6	$82.4	12.0%
														Client Type
														Sub-Total	Totals
Public	#	5	2	0	7	35%		0	0	0	0	0%		35%
Public	$	$447.4	$49.2	$0.0	$496.6	72.2%		$0.0	$0.0	$0.0	$0.0	0%		72.2%	$496.6
Corporate	#	2	0	0	2	10%		0	0	0	0	0%		10%
Corporate	$	$77.5	$0.0	$0.0	$77.5	11.3%		$0.0	$0.0	$0.0	$0.0	0%		11.3%	$77.5
Personal	#	0	0	2	2	10%		4	0	1	5	25%		35%
Personal	$	$0.0	$0.0	$4.0	$4.0	0.6%		$63.1	$0.0	$4.6	$67.8	10%		10.4%	$71.8
Sub-Adv	#	1	0	0	1	5%		0	0	0	0	0%		5%
Sub-Adv	$	$23.2	$0.0	$0.0	$23.2	3.4%		$0.0	$0.0	$0.0	$0.0	0%		3.4%	$23.2
PrivateFund	#	0	0	0	0	0%		2	0	0	2	10%		10%
PrivateFund	$	$0.0	$0.0	$0.0	$0.0	0%		$14.6	$0.0	$0.0	$14.6	2%		2.1%	$14.6
CIT	#	1	0	0	1	100%		0	0	0	0	0%		100%
CIT	$	$3.9	$0.0	$0.0	$3.9	0.6%		$0.0	$0.0	$0.0	$0.0	0%		0.6%	$3.9
															$687.5

 

[Toadstool]

You don't say how your quarters are calculated so I've done a lookup table. Obviously you also need to specify the year you want to that table is in tab "FYQtrs"

NinaE_11.xlsx
	A	B	C
1	FYQtr	Start	End
2	2018-Q1	01-Jan-18	31-Mar-18
3	2018-Q2	01-Apr-18	30-Jun-18
4	2018-Q3	01-Jul-18	30-Sep-18
5	2018-Q4	01-Oct-18	31-Dec-18
6	2019-Q1	01-Jan-19	31-Mar-19
7	2019-Q2	01-Apr-19	30-Jun-19
8	2019-Q3	01-Jul-19	30-Sep-19
9	2019-Q4	01-Oct-19	31-Dec-19
FYQtrs

Cell Formulas
Range	Formula
C2:C9	C2	=EOMONTH(B2,2)
B3:B9	B3	=C2+1

Here's the dummy data I used: in tab "Data":

NinaE_11.xlsx
	A	B	C	D	E
1	Account	Size	Composite	Date	Value
2	XC6	Small	Inside	1/3/2018	$ 166
3	XC9	SMID	Outside	1/25/2018	$ 189
4	XC12	SMID	Outside	1/25/2018	$ 327
5	XC15	Large	Outside	2/16/2018	$ 212
6	XC18	Small	Outside	3/10/2018	$ 373
7	XC21	Small	Outside	5/5/2018	$ 235
8	XC24	SMID	Outside	5/27/2018	$ 258
9	XC27	SMID	Outside	6/16/2018	$ 350
10	XC30	SMID	Outside	6/16/2018	$ 396
11	XC33	SMID	Outside	1/4/2019	$ 281
12	XC36	SMID	Inside	1/24/2019	$ 419
13	XC39	Small	Inside	8/14/2019	$ 304
Data

On the "Summary" tab I've done the dropdown then retrieved the Start/End dates for that quarter so they're used to select using SUMIFS, COUNTIFS, etc.

NinaE_11.xlsx
	A	B	C	D	E	F	G	H	I	J
1	FYQtr		Start Date	End Date		Total Firm Assets (TFA)
2	2018-Q2		01-Apr-18	30-Jun-18			Small	SMID	Outside
3							Cap	Cap	Composite	Totals
4						Accts (#)	1	3	4	8
5						Assets ($)	$235.00	$1,004.00	$1,239.00	$2,478.00
Summary

Cell Formulas
Range	Formula
C2:D2	C2	=INDEX(FYQtrs!B$2:B$9,MATCH($A$2,FYQtrs!$A$2:$A$9,0))
G4:H4	G4	=COUNTIFS(Data!$D:$D,">="&$C$2,Data!$D:$D,"<="&$D$2,Data!$B:$B,G$2)
I4	I4	=COUNTIFS(Data!$D:$D,">="&$C$2,Data!$D:$D,"<="&$D$2,Data!$C:$C,I$2)
J4:J5	J4	=SUM(G4:I4)
G5:H5	G5	=SUMIFS(Data!$E:$E,Data!$D:$D,">="&$C$2,Data!$D:$D,"<="&$D$2,Data!$B:$B,G$2)
I5	I5	=SUMIFS(Data!$E:$E,Data!$D:$D,">="&$C$2,Data!$D:$D,"<="&$D$2,Data!$C:$C,I$2)

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=FYQtrs!$A$2:$A$9

 

[NinaE_11]

Wow, thank you for this! I will study and hopefully I can follow to apply to my project! I sincerely appreciate all your work and insight on this - great work!
Thank you again.