Dashboard

[Ken1004]

Hi, can anyone suggest what is the best approach to tackle what I'm trying to accomplish? I have 8 columns of header and 132 rows of data per month starting January. I want to create on a separate sheet a dashboard so that the user can use a dropdown list to select 'Month' and another dropdown list to select 'Area', another dropdown list for 'Sub-Area' and another dropdown list for 'Type'. Based on these selections, it will search the database and produce data that results in Sales, Active, HPI, & 1yr Change. Here is a sample chart below.

[TABLE="width: 870"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Area[/TD]
[TD]Sub-Area[/TD]
[TD]Type[/TD]
[TD]Sales[/TD]
[TD]Active[/TD]
[TD]HPI[/TD]
[TD]1 yr Change[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Area1[/TD]
[TD]Burke Mountain[/TD]
[TD]Detach[/TD]
[TD]10[/TD]
[TD]73[/TD]
[TD]$1,495,900[/TD]
[TD]19.7%[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Area1[/TD]
[TD]Burke Mountain[/TD]
[TD]Townhouse[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]$831,200[/TD]
[TD]21.0%[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Area1[/TD]
[TD]Burke Mountain[/TD]
[TD]Condo[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$0[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Area2[/TD]
[TD]Canyon Springs[/TD]
[TD]Condo[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]$485,400[/TD]
[TD]32.7%[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Area2[/TD]
[TD]Cape Horn[/TD]
[TD]Detach[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]$1,055,900[/TD]
[TD]16.8%[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Area3[/TD]
[TD]Cape Horn[/TD]
[TD]Townhouse[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$0[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Area3[/TD]
[TD]Cape Horn[/TD]
[TD]Condo[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$0[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]Area1[/TD]
[TD]Westwood Plateau[/TD]
[TD]Condo[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]$566,800[/TD]
[TD]26.9%[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]Area1[/TD]
[TD]Westwood Summit CQ[/TD]
[TD]Condo[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]$0[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]Area2[/TD]
[TD]Birchland Manor[/TD]
[TD]Detach[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]$914,000[/TD]
[TD]16.3%[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]Area2[/TD]
[TD]Central Pt Coquitlam[/TD]
[TD]Detach[/TD]
[TD]0[/TD]
[TD]13[/TD]
[TD]$829,800[/TD]
[TD]15.5%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]Area1[/TD]
[TD]North Coquitlam[/TD]
[TD]Detach[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]$0[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]Area1[/TD]
[TD]Park Ridge Estates[/TD]
[TD]Detach[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1,410,000[/TD]
[TD]14.4%[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]Area2[/TD]
[TD]Ranch Park[/TD]
[TD]Detach[/TD]
[TD]4[/TD]
[TD]17[/TD]
[TD]$1,211,300[/TD]
[TD]14.6%[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]Area2[/TD]
[TD]River Springs[/TD]
[TD]Detach[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]$861,200[/TD]
[TD]13.5%[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance.

 

[Fluff]

Hi & welcome to MrExcel
How about

Excel 2013/2016
	A	B	C	D	E	F
1	Month	January	Sales	Active	HPI	1 yr Change
2	Area	Area1	5	5	$831,200	0.21
3	Sub-Area	Burke Mountain
4	Type	Townhouse
Sheet2

Cell Formulas
Range	Formula
C2	=IFERROR(INDEX(Template!E$2:E$18,AGGREGATE(15,6,ROW(Template!$A$2:$A$18)-ROW(Template!$A$2)+1/((Template!$A$2:$A$18=$B$1)*(Template!$B$2:$B$18=$B$2)*(Template!$C$2:$C$18=$B$3)*(Template!$D$2:$D$18=$B$4)),ROWS($1:1))),"")

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Month	Area	Sub-Area	Type	Sales	Active	HPI	1 yr Change
2	January	Area1	Burke Mountain	Detach	10	73	$1,495,900	19.70%
3	January	Area1	Burke Mountain	Townhouse	5	5	$831,200	21.00%
4	January	Area1	Burke Mountain	Condo	0	0	$0	0.00%
5	January	Area2	Canyon Springs	Condo	5	2	$485,400	32.70%
6	January	Area2	Cape Horn	Detach	1	7	$1,055,900	16.80%
7	January	Area3	Cape Horn	Townhouse	0	0	$0	0.00%
8	January	Area3	Cape Horn	Condo	0	0	$0	0.00%
9
10	February	Area1	Westwood Plateau	Condo	5	10	$566,800	26.90%
11	February	Area1	Westwood Summit CQ	Condo	0	0	$0	0.00%
12	February	Area2	Birchland Manor	Detach	2	0	$914,000	16.30%
13	February	Area2	Central Pt Coquitlam	Detach	0	13	$829,800	15.50%
14
15	March	Area1	North Coquitlam	Detach	0	2	$0	0.00%
16	March	Area1	Park Ridge Estates	Detach	1	1	$1,410,000	14.40%
17	March	Area2	Ranch Park	Detach	4	17	$1,211,300	14.60%
18	March	Area2	River Springs	Detach	1	0	$861,200	13.50%
Template

 

[Ken1004]

Awesome, it worked and thank you!

 

[Fluff]

You're welcome & thanks for the feedback