Dynamic Chart with drop down list

**finlain** · 07-16-2008, 03:31 PM

Hi
I have a chart example that allows the user to click an item in the drop down list and a different chart displays.
They have example code using offset to select each item of data. Does anyone know how to get the list to select the correct data to chart?

I am attaching an exampleof some of the code being used. I have looked at several "Google" results and no luck

Thanks
Elaine

A2 CONCATENATE("Results for ",INDEX(A6:A11,A4))
Data OFFSET(Chart!$A$4,Chart!$A$4,1,1,4)
Metric_Name OFFSET(Chart!$A$4,Chart!$A$4,0,1,1)
Time Conversion 60*(MID(B12,6,2))+MID(B12,9,2)

**Andy Pope** · 07-16-2008, 03:48 PM

can you post an actual workbook?

**finlain** · 07-17-2008, 08:33 AM

I tried sending the working file yesterday and it was too large. I am attempting to make it smaller.
Thanks
Elaine

**Andy Pope** · 07-17-2008, 09:04 AM

your chart example does update when you select an item in the list.

What exactly is the problem?

**finlain** · 07-17-2008, 09:07 AM

Hi Andy
The example that I gave you is not mine, I do not know how exactly to do this. I have the list box but the one I created is not working dynamically.
I'd try and send that file but I think it is too large.

Thanks
Elaine

**Andy Pope** · 07-17-2008, 09:20 AM

okay.

The chart uses named ranges to specify where to find the correct data.

The steps required to construct the example would be,

Create named ranges for labels and data
Create and format chart
Base chart on named ranges
create listbox

Which step do you need help on?
How different is your data compared to the example you attached?

**finlain** · 07-17-2008, 12:56 PM

Hi Andy
I did the first step and I know how to create and format the chart, but I am not sure of the third step here. How do I base a chart on named rages? I know it is easier than I am making it and I really appreciate your help!

Create named ranges for labels and data
Create and format chart
Base chart on named ranges
create listbox

Which step do you need help on?

**Andy Pope** · 07-17-2008, 02:38 PM

After you have created a chart based on the fixed range A4:F5 use the Source Data dialog, Series tab, to change the series information.

The series name would use the formula
='Dynamic Graphs'!metric_Name

Values would use the formula
=chart.xls!Data

**finlain** · 07-17-2008, 03:37 PM

Hi Andy
I am not getting it. If you can do one series, I'll be on my way. Here is the chart that I am actually working on.

Thanks a million!

**Andy Pope** · 07-17-2008, 03:49 PM

First fix the list boxes Linked Cell property so selecting an item updates A4

add 3 named ranges,

LABELS
=OFFSET(Insurance!$A$4,0,1,1,5)

DATA
=OFFSET(LABELS,Insurance!$A$4,0)

Metric_Name
=OFFSET(Insurance!$A$4,Insurance!$A$4,0,1,1)

Set the series formula to be,
=SERIES('650078.xls'!Metric_Name,'650078.xls'!LABELS,'650078.xls'!DATA,1)

The chart title is actually linked to the named range Metric_Name.

**finlain** · 07-22-2008, 03:08 PM

Hi Andy
First thank you so much for your patience with this! I've done everything that you sent in the thread and the last item that is not working is the chart title. I saw in your note where you stated "Set the series formula to be,
=SERIES('650078.xls'!Metric_Name,'650078.xls'!LABELS,'650078.xls'!DATA,1)

The chart title is actually linked to the named range Metric_Name.

But I did not see that in your chart and yours works perfectly.

I am resending mine to see if you can see anything

Again thank you!

Elaine

**Andy Pope** · 07-23-2008, 04:21 AM

Your chart title is current just static text. You need to link the chart title to the named range.
This explains how to link to a cell. And the reference would be

=NewReport.xls!Metric_Name

**finlain** · 07-23-2008, 08:42 AM

Andy
Got it...thanks again for your help!

Elaine

**mhuston** · 05-19-2009, 11:44 AM

Andy, I am trying to make two drop downs, graphing two lines on the same graph. I am using:
LABELS OFFSET(ProjChart!$A$2,0,1,1,7)
LABELS1 OFFSET(ProjChart!$A$6,0,1,1,7)
DATA OFFSET(LABELS,$A$2,0)
DATA1 OFFSET(LABELS1,$A$6,0)
Metric_Name OFFSET($A$2,$A$2,0,1,1)
Metric_Name1 OFFSET($A$6,$A$6,0,1,1)
Chart values: Metric_Name,DATA
Chart values1: Metric_Name1,DATA1
Where the first group of data is in cells A2:U5, with row 2 containing dates; the second group is in cells A6:U9, with row 6 containing the same dates.
I tried to set graph data as ProjChart!Metric_Name = ProjChart!DATA and ProjChart!Metric_Name1 = ProjChart!DATA1, but only the top set of data displays.

**Andy Pope** · 05-19-2009, 11:48 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Please start a new thread.