Solved

# Excel Dynamic Chart report

Posted on 2009-12-26
303 Views
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
Question by:Theva

LVL 45

Accepted Solution

Theva,

Updated file attached.

Patrick
Weekly-Dashboard-Report-Ver1-01.xls
0

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

LVL 80

Assisted Solution

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.

Weekly-Dashboard-Report-Ver1-01Q.xls
0

Author Closing Comment

Hi,

Thanks a lot and its really superb.
0

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
0

## Featured Post

### Suggested Solutions

This script will sweep a range of IP addresses (class c only, 255.255.255.0) 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…