Excel Dynamic Chart report

Posted on 2009-12-26
Last Modified: 2012-06-21
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.
Question by:Theva
    LVL 45

    Accepted Solution


    Updated file attached.


    Author Comment

    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.  
    LVL 80

    Assisted Solution

    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.


    Author Closing Comment


    Thanks a lot and its really superb.
    LVL 45

    Expert Comment

    Theva - Thanks for the points and grade. I'm pleased to see that Brad concluded the matter in my absence. - Patrick

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This script will sweep a range of IP addresses (class c only, and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now