dashboard help

[HanSam]

Hi all,

Would like to get some help from you regarding the file attached.
The objectives are:

1. To have 4 dependent drop down lists
   • Location
   • Workstream
   • Process
   • Matrix
2. After the drop down values are selected, the data from M to AA should be filtered according to the drop down options.
3. What is the best way to layout the data source for this kind of situation?

Thanks!

 

[AVK]

Firstly in "data" sheet in column "L"
1) Go to Sheet name "dashboard"
2) Select a cell in an unused part of the sheet (Suppose select cell U3).
3) On the Excel Ribbon's Data tab, click Advanced
4) Choose Copy to another location.
5) Click in the List Range box.
6) In List Range box, go to "Data" sheet and select the database from $L$1:$AA$70.
7) Click in the Criteria range box.
8) Select the criteria range from sheet "dashboard" $U$1:$U$2
9) Click in the Copy to box.
10) Select the cell "$A$1:$P$1"on Sheet name "dashboard" suppose "A1"
11) Click OK

 

[HanSam]

Hi @AVK

Not quite what I imagined. I've attached another file in which I rewrite the datasource. Also I've included the layout of what I imagine it looks like.

The dropdown lists should be dependent from each other. The dependencies are on the HelpFile sheet on the file.

The details should be on the file itself.

 

[AVK]

Hello HanSam : plz enter manually data in "details" sheet. like ---->
In Details sheet :
If SSC is "Maxico" then which data listed in KPI Details, Status, Trend, Target, Actual, Target D%.

Secondly in current attach file, where is data from Sep-15 to Aug-16.

What is meaning of "Ave'15? (In details sheet "AC6")

 

[HanSam]

@AVK so there are 4 dropdown right. If one of the SSC is chosen, the workstream under that ssc will show up on the kpi details. If one of the ssc is chosen and one workstream is chosen, all the process under that workstream under the ssc will show up. and then if one process is chosen the matrix will show up on the kpi detail. it is like folder explorer approach.

Status & Trend will be icon based.


all the data for the months are on the data tab. I am not sure if that is the best way to write the source data.

Ave '15 is just an average of all of 2015. You can just create a dummy for that but they are percentages. However, if you will look at the source data, whenever you see volume on the matrix, the value is number. Timeliness & Quality should be percentile, I just converted them into numbers thinking it is better to format after.

I appreciate you taking time on this. I've did numerous research but it seems that I still need to learn a lot to be able to put this together.

 

[HanSam]

Hi @AVK

Did you make any progress? I my self is working on the file, but even the dependent dropdown boxes is giving me a tough time.

 

[AVK]

Dear Hansam, i will come back shortly, i am busy in our family programme. plz wait.

 

[HanSam]

Thanks @AVK !

 

[AVK]

Dear Hansam, I attach similar file for you. This will help you.

 

[HanSam]

I can't seem to find help from that file but thank you.

 

[poseidonsbeard]

I am trying to create dashboard that will accomplish the following objectives for construction industry manpower:

1. Project potential manpower by week
2. Track realized manpower by week
3. Track manpower cost per week
4. Reveal manpower trends
5. Track overall labor cost
6. Reveal overall cost trends

Plus having dynamic charts for presentation purposes. I have attached a .xlsx document with data for reference.