• 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.

Dashboard in Excel

D

Deepak Chhabra

New Member
Hi,

I am facing challenge while creating Dashboard in Excel.

I have below selection criteria in main window

Open Year Category Closed Year Priority
2013 A 2013 High
2014 B 2014 Low
2015 C 2015 Medium
All (2013, 2014 & 2015) All (A, B & C) All Critical
All


Please let me know how can I calculate the values for All in all the header in graph.

I don't want to use concatenate option.

Thanks in Advance.

Regards,
DC
 
Hi Deepak,

See the attached file. I had done some modification in your dropdown list like converting number to text and changed the formula for first graph data.

If this what you want you can change other formulas also.

Just advise if any issue.

P.S. all formulas are array formula so must be entered with Ctrl+Shift+Enter ad not just enter.

Regards,
 

Attachments

  • DC_Chandoo.xlsx
    89 KB · Views: 38
Somendra Misra said:
Hi Deepak,

See the attached file. I had done some modification in your dropdown list like converting number to text and changed the formula for first graph data.

If this what you want you can change other formulas also.

Just advise if any issue.

P.S. all formulas are array formula so must be entered with Ctrl+Shift+Enter ad not just enter.

Regards,
Click to expand...
Hi Somendra Misra,

Thanks for your prompt response.

My concern here is that if the user selected all from data validation list for open year , closed year, category and selected priority as high or all. It should show all the values as per the list selections in charts.

I want to select values as Open & Closed Year 2013, category and priority as all.

This is one of the example. we may such kind of other scenarios also.

Kindly help me out to complete this task.

Regards,
DC
 
Hi Deepak,

If you see the file I attached in my last post. The first graph is doing exactly what you want.

What else issue you have?

Regards,
 
Hi Somendra,

In your attachment, there is no list selection.

Could you please help me out with that.

These are below scenarios for all the graphs in the dashboard sheet

Category: All (Enhancements, Incident, Request)
Open Year: All (2013 , 2014 etc..)
Closed Year: All (2013 , 2014 etc..)
Priority: All (High, Low, Medium, Critical, Emergency etc.)

User can select either of the above selection criteria as all values or selection may be as (2013 + 2014 (Open Year & Closed Year), Category as "All" and Priority as "High", but in the attached file, I couldn't able to select any value.

I really appreciate your effort to solving me out this high priority task.

Thanks & Regards,
DC
 

Attachments

  • upload_2014-3-8_23-13-53.png
    upload_2014-3-8_23-13-53.png
    145.7 KB · Views: 13
Somendra Misra said:
Hi,

See the Dashboard Sheet. All the original list are maintained.

Regards,
Click to expand...
Dear Sir,

I am unable to see the list in the dashboard sheet.

I am attaching the snapshots for your perusal.

Sorry for trouble.

Thanks & Regards,
Deepak Chhabra
 

Attachments

  • Capture.PNG
    Capture.PNG
    92.8 KB · Views: 5
  • Capture1.png
    Capture1.png
    114 KB · Views: 6
Hi Deepak,

Just jumping.. i have not read all the topics.. but..

I just use Find & Replace in Sheet > List.
Find : All
Replace : *

As in Pre-Processing Sheet.. all formula's are SumIf / CountIF, They works file with "*" and large DataSet(unlike Sumproduct)..

Please let us know.. If we need to verify / work on your calculation sheet.
 

Attachments

  • Sample(3).xlsx
    84.8 KB · Views: 13
Hi ..

It looks like..
In Excel 2007 and prior, For Data Validation from another sheet, we have to use "Named Range"..

However, In In 2007+ for DV, another Sheet, "=List!$C$4:$C$15" is working fine..

Please find the attached sheet..
J3, M3, P3, & S3 having DV with Named Range,
and J5, M5, P5, S5 having DV as "=List!$C$4:$C$15"

I think, 2007 & prior version excel will not able to see the DV in J5 : S5,

Please confirm..
 

Attachments

  • DC_Chandoo.xlsx
    84.9 KB · Views: 10
Hi Debraj ,

I am using Excel 2007 ; when I click on Data Validation , and in the formula box , try to click on a different sheet tab , Excel does not allow it ; however , it allows me to enter =List!$C$4:$C$15 directly into the formula box , and the DV works without any problem.

Narayan
 
Hi Debraj ,

When I opened the file , the DV drop-down was not present ; when I reintroduced it using the formula =List!$C$4:$C$15 , it works.

Narayan
 
You must log in or register to reply here.
Back
Top