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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Month</td><td style=";">January</td><td style=";">Sales</td><td style=";">Active</td><td style=";">HPI</td><td style=";">1 yr Change</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Area</td><td style=";">Area1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">$831,200</td><td style="text-align: right;;">0.21</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sub-Area</td><td style=";">Burke Mountain</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Type</td><td style=";">Townhouse</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Template!E$2:E$18,AGGREGATE(<font color="Green">15,6,ROW(<font color="Purple">Template!$A$2:$A$18</font>)-ROW(<font color="Purple">Template!$A$2</font>)+1/(<font color="Purple">(<font color="Teal">Template!$A$2:$A$18=$B$1</font>)*(<font color="Teal">Template!$B$2:$B$18=$B$2</font>)*(<font color="Teal">Template!$C$2:$C$18=$B$3</font>)*(<font color="Teal">Template!$D$2:$D$18=$B$4</font>)</font>),ROWS(<font color="Purple">$1:1</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Month</td><td style=";">Area</td><td style=";">Sub-Area</td><td style=";">Type</td><td style=";">Sales</td><td style=";">Active</td><td style=";">HPI</td><td style=";">1 yr Change</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">January</td><td style=";">Area1</td><td style=";">Burke Mountain</td><td style=";">Detach</td><td style="text-align: right;;">10</td><td style="text-align: right;;">73</td><td style=";">$1,495,900</td><td style="text-align: right;;">19.70%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">January</td><td style=";">Area1</td><td style=";">Burke Mountain</td><td style=";">Townhouse</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">$831,200</td><td style="text-align: right;;">21.00%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">January</td><td style=";">Area1</td><td style=";">Burke Mountain</td><td style=";">Condo</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">$0</td><td style="text-align: right;;">0.00%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">January</td><td style=";">Area2</td><td style=";">Canyon Springs</td><td style=";">Condo</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style=";">$485,400</td><td style="text-align: right;;">32.70%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">January</td><td style=";">Area2</td><td style=";">Cape Horn</td><td style=";">Detach</td><td style="text-align: right;;">1</td><td style="text-align: right;;">7</td><td style=";">$1,055,900</td><td style="text-align: right;;">16.80%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">January</td><td style=";">Area3</td><td style=";">Cape Horn</td><td style=";">Townhouse</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">$0</td><td style="text-align: right;;">0.00%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">January</td><td style=";">Area3</td><td style=";">Cape Horn</td><td style=";">Condo</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">$0</td><td style="text-align: right;;">0.00%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">February</td><td style=";">Area1</td><td style=";">Westwood Plateau</td><td style=";">Condo</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td><td style=";">$566,800</td><td style="text-align: right;;">26.90%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">February</td><td style=";">Area1</td><td style=";">Westwood Summit CQ</td><td style=";">Condo</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">$0</td><td style="text-align: right;;">0.00%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">February</td><td style=";">Area2</td><td style=";">Birchland Manor</td><td style=";">Detach</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style=";">$914,000</td><td style="text-align: right;;">16.30%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">February</td><td style=";">Area2</td><td style=";">Central Pt Coquitlam</td><td style=";">Detach</td><td style="text-align: right;;">0</td><td style="text-align: right;;">13</td><td style=";">$829,800</td><td style="text-align: right;;">15.50%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">March</td><td style=";">Area1</td><td style=";">North Coquitlam</td><td style=";">Detach</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style=";">$0</td><td style="text-align: right;;">0.00%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">March</td><td style=";">Area1</td><td style=";">Park Ridge Estates</td><td style=";">Detach</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style=";">$1,410,000</td><td style="text-align: right;;">14.40%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">March</td><td style=";">Area2</td><td style=";">Ranch Park</td><td style=";">Detach</td><td style="text-align: right;;">4</td><td style="text-align: right;;">17</td><td style=";">$1,211,300</td><td style="text-align: right;;">14.60%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">March</td><td style=";">Area2</td><td style=";">River Springs</td><td style=";">Detach</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style=";">$861,200</td><td style="text-align: right;;">13.50%</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Template</p><br /><br />
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,530
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,056
Members
409,799
Latest member
camronmartin

This Week's Hot Topics

Top