Dashboard

Ken1004

New Member
Joined
Mar 5, 2019
Messages
7
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.

MonthAreaSub-AreaTypeSalesActiveHPI1 yr Change
JanuaryArea1Burke MountainDetach1073$1,495,90019.7%
JanuaryArea1Burke MountainTownhouse55$831,20021.0%
JanuaryArea1Burke MountainCondo00$00.0%
JanuaryArea2Canyon SpringsCondo52$485,40032.7%
JanuaryArea2Cape HornDetach17$1,055,90016.8%
JanuaryArea3Cape HornTownhouse00$00.0%
JanuaryArea3Cape HornCondo00$00.0%
FebruaryArea1Westwood PlateauCondo510$566,80026.9%
FebruaryArea1Westwood Summit CQCondo00$00.0%
FebruaryArea2Birchland ManorDetach20$914,00016.3%
FebruaryArea2Central Pt CoquitlamDetach013$829,80015.5%
MarchArea1North CoquitlamDetach02$00.0%
MarchArea1Park Ridge EstatesDetach11$1,410,00014.4%
MarchArea2Ranch ParkDetach417$1,211,30014.6%
MarchArea2River SpringsDetach10$861,20013.5%

<tbody>
</tbody>

Thank you in advance.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,303
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
How about


Excel 2013/2016
ABCDEF
1MonthJanuarySalesActiveHPI1 yr Change
2AreaArea155$831,2000.21
3Sub-AreaBurke Mountain
4TypeTownhouse
Sheet2
Cell Formulas
RangeFormula
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
ABCDEFGH
1MonthAreaSub-AreaTypeSalesActiveHPI1 yr Change
2JanuaryArea1Burke MountainDetach1073$1,495,90019.70%
3JanuaryArea1Burke MountainTownhouse55$831,20021.00%
4JanuaryArea1Burke MountainCondo00$00.00%
5JanuaryArea2Canyon SpringsCondo52$485,40032.70%
6JanuaryArea2Cape HornDetach17$1,055,90016.80%
7JanuaryArea3Cape HornTownhouse00$00.00%
8JanuaryArea3Cape HornCondo00$00.00%
9
10FebruaryArea1Westwood PlateauCondo510$566,80026.90%
11FebruaryArea1Westwood Summit CQCondo00$00.00%
12FebruaryArea2Birchland ManorDetach20$914,00016.30%
13FebruaryArea2Central Pt CoquitlamDetach013$829,80015.50%
14
15MarchArea1North CoquitlamDetach02$00.00%
16MarchArea1Park Ridge EstatesDetach11$1,410,00014.40%
17MarchArea2Ranch ParkDetach417$1,211,30014.60%
18MarchArea2River SpringsDetach10$861,20013.50%
Template
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,303
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,305
Messages
5,836,534
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top