• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

interactive dashboard

kamil

New Member
Hi Guys,

I am trying to create an interactive dashboard but being an unexperienced person with excel functionality I ham facing some challenges and wondering if one of you can help me out.
I am attaching a sample data file that contains two tabs (data and dashboard). By looking at dashboard tab you will know what I am trying to build.

let me know if its not clear or if you have any questions.

Really appreciate your help!

thanks
 

Attachments

  • sample data.xlsx
    20.6 KB · Views: 46
I didn't understand the 3rd filter option about Criticality. :( but it looks like most of the options could be calculated by using some PivotTable(s) (see the extra sheet I added) and then use the GETPIVOTDATA function to extract the information you need. If you can generate a PT for the Criticality option, one idea might be to use Chandoo dynamic chart hiding/unhiding method, but use it to hide PT's instead.
http://chandoo.org/wp/2013/04/23/interactive-chart-in-excel-tutorial/
 
Thanks Luke. You mentioned that you have added an extra sheet in your response but i dont see any attachment?
Regards,
 
Oops, sorry about that. Check now.
 

Attachments

  • Example sample data.xlsx
    32.6 KB · Views: 98
Thanks Luke.
This is great...
Challenge that I am having is to make this interactive. I can get the data by using countif or sumif formulas but problem i am facing is, how to make it interactive based on the drop down value as i am trying to do on Dashboard tab.

Even if i use GetPivotData formula how do i change the values based on what i select on the drop down?

Regards,
 
To elaborate on what I said before, the idea would be to create multiple PivotTables for the 4 different options you have. Those PT's are positioned specially in the worksheet, and then your control feature lets you hide/unhide the different PTs as you need them.
Alternatively, The GETPIVOTDATA function will accept cell references for some the arguments, so you could use that method to pull different data points from the PT as dropdowns change.
 
how do I tie drop down values to change the data on the target cells that I get from getpivotdata?
 
Example of a static GETPIVOTDATA formula, pulling info from a PivotTable in A3:
=GETPIVOTDATA("Score",$A$3,"Name","Bob","Answer",3)

Returns the Score from the intesection where Bob has an answer of 3. Changing this to make the choices variable:
=GETPIVOTDATA("Score",$A$3,"Name",D1,"Score",E1)

Now, the name that we pick in D1 and the Answer we pick in E1 will change what the formula returns.
 
Thanks Luke.

Is this a generic example or you are refering to some excel sheet (which contain the data Name and Score)? missing the file if you are refering to one.
 
Just a generic example to show how you can make the arguments within GETPIVOTDATA into variables.
 
Back
Top