Project Dashboard

[petro62]

I am trying to create a clean dashboard for our group to quickly go through projects and get status updates. We have a corporate dashboard which is fine for entering data, but horrible for reviewing because it is 200+ rows and 60 plus columns of different data. I was trying to build a dashboard similar in style of what is on excelfind.com

So I have the sheet of data and from that I assume I can create the pivot tables and pivot charts for the specific items I want to display. The question I have is how do I set all of those pivot tables filters by selecting from a drop down list on the main dashboard.

We open up the dashboard. I want to review Project Test1. I select Project Test 1 in a drop down list on the main dashboard page and when I do that it sets filters for all the pivot tables to Project Test 1 so that then all the data that is displayed on dashboard is for Project Test 1.


I think I can figure out the pivot table and charts but was stumped on this filter option.

 

[camascari]

This is what I use for a similar project:
(I'm sort of a newbie to VBA so please test this out on a duplicate workbook, just in case.)

Right click worksheet that contains the dashboard/ drop-down list > "view code" > Insert this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Target.Parent.Range("DropDownLocation")
    If Not Intersect(Target, rng) Is Nothing Then
        Select Case Range("DropDownLocation")
            Case "Project1": Project1
            Case "Project2": Project2
        End Select
    End If
    End Sub

Then in a general module add the following (add more of these for each project "case" listed above):

Sub Project1()
'
' Project1 Macro
'

'
    Application.ScreenUpdating = False
    Sheets("ProjectDataSheet").Select
    ActiveSheet.Range("DataRange").AutoFilter Field:=16, Criteria1:="Project1"
    Application.ScreenUpdating = True
    Sheets("Dashboard").Select
End Sub

Make sure that "16" gets changed to whatever column # the project # is listed in. If you just name it "1", "2", not "project1", "project2" then also make sure you change the criteria to match.

Hope this helps!