• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

Excel Dynamic Chart report

Hi Experts,

I need Experts help to create a dynamic charts based on sheet-1 data "Time Index". Dynamic chart has been created based on "Name" (please refer the attached excel sheet), but I've no clue how to produce the similar chart for other tasks. I've created the data frame for each task and I need Experts help to map the data into the respective charts.

Hope you can help me. I've attached the excel sheet for your perusal.
Weekly-Dashboard-Report-Ver1.xls
0
Theva
Asked:
Theva
  • 2
  • 2
2 Solutions
 
patrickabCommented:
Theva,

Updated file attached.

Patrick
Weekly-Dashboard-Report-Ver1-01.xls
0
 
ThevaAuthor Commented:
Hi patrickab,

Thanks for the help, however, I noticed the chart not showing the specific data. Let say, if we select Scheduling, all the relevant data for scheduling need to be displayed, Other data, Media Processing/Work Order,Localization, Channel Ops & Admin should not appears. Its only displayed when we specifically select the task.  
0
 
byundtCommented:
Theva,
I generalized Patrick's work by adding an additional criterion for the SUMPRODUCT formulas. This criterion required the Task in the data validation dropdown to agree with the column header on the Time Index worksheet. After copying these revised formulas across and down, Patrick's charts updated automatically to the task you choose in the dropdown.

For example, cell L4 formula is:
=SUMPRODUCT(('Time Index'!$A$2:$A$65000='Weekly Productivity Report'!L$3)*('Time Index'!$B$2:$B$65000='Weekly Productivity Report'!$I4)*(Tasks='Weekly Productivity Report'!$J$2)*'Time Index'!$E$2:$H$65000)

And cell D36 formula is:
=SUMPRODUCT(('Time Index'!$A$2:$A$65000='Weekly Productivity Report'!D$35)*('Time Index'!$B$2:$B$65000='Weekly Productivity Report'!$A36)*(Tasks=$B$34)*'Time Index'!$E$2:$H$65000)

And cell L36  formula is:
=SUMPRODUCT(('Time Index'!$A$2:$A$65000='Weekly Productivity Report'!L$35)*('Time Index'!$B$2:$B$65000='Weekly Productivity Report'!$I36)*(Tasks=$J$34)*'Time Index'!$E$2:$H$65000)

In all of these formulas, the named range Tasks refers to Time Index cells E1:H1. By creating this named range, I was also able to use it as a Source in each of the data validation dropdowns.

While trolling through your worksheet, I noticed extra blank spaces in the column headers in named range Tasks, and also in the Week numbers in both worksheets. I eliminated these blank spaces.

Brad
Weekly-Dashboard-Report-Ver1-01Q.xls
0
 
ThevaAuthor Commented:
Hi,

Thanks a lot and its really superb.
0
 
patrickabCommented:
Theva - Thanks for the points and grade. I'm pleased to see that Brad concluded the matter in my absence. - Patrick
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now