I have a set of columns and each contains a list of parameters that
describe a scenario. (Not any Excel-specific meaning of "scenario" (I
think there is such a thing), but just the ordinary meaning of "it
could be like this, or like this, or this...." parameter lists).

I may input the descriptions of ten different scenarios, and then I
have a set of charts for viewing the various implications of the
scenarios. Having ten lines on a single chart is way too confusing.
I'm usually interested in contrasting just two or three of them at a
time. If this were an ordinary app, I'd have a checkbox above each
column of parameters that would stand for "include in charts? yes or
no?".

I'm not quite sure how to do such a thing with Excel. I'm a programmer
(C++, C#, Lisp, etc.), but I'm a novice at Excel, so the simpler the
solution, the better. I realize that I can get the macro recorder to
write some useful code for me, and then I can try to tweak the code,
but I'm not even sure what I would do manually that I could record to
get code for adding and removing series from a set of charts (not just
one) in response to a "yes" or "no" in a cell.

I'd be grateful for any suggestions.

Thanks.

Tuang wrote:
I have a set of columns and each contains a list of parameters that
describe a scenario. (Not any Excel-specific meaning of "scenario" (I
think there is such a thing), but just the ordinary meaning of "it
could be like this, or like this, or this...." parameter lists).

I may input the descriptions of ten different scenarios, and then I
have a set of charts for viewing the various implications of the
scenarios. Having ten lines on a single chart is way too confusing.
I'm usually interested in contrasting just two or three of them at a
time. If this were an ordinary app, I'd have a checkbox above each
column of parameters that would stand for "include in charts? yes or
no?".

I don't understand exactly what you're charting. But let me throw out a few
suggestions.

First, you could transpose your data. One row would be a set of parameters,
each row a different scenario. Then you could filter the data to exclude
any unwanted scenarios.

Second, keeping your existing data set, you could hide columns. You could
set the chart to not plot hidden cells, which would eliminate the unwanted
scenarios.

Finally, you might wish to consider a pivot chart. The pivot chart gives a
check-box list that allows you to select the data that appears on the chart.
I think that might be the slickest option. If you're unfamiliar with pivot
charts, go see this site:
http://www.geocities.com/jonpeltier/...pivotstart.htm

Dave
dvt at psu dot edu

"dvt" wrote in message ...


I don't understand exactly what you're charting. But let me throw out a few
suggestions.

First, you could transpose your data...

No, no. I'm sorry I wasn't clear. I have a page (meaning worksheet)
of inputs. Imagine row1 being something like "NaCl concentration",
row2 being "starting temperature", row3 being "voltage", etc., and
each column contains a different set of these parameters. There's a
column with values for a scenario called "Option A", one to its right
called "Option B", etc., each column containing some value for each of
the input parameters.

Those inputs feed into several pages of models. The models do lots of
analysis and send their outputs to several summary worksheets. Imagine
one summary worksheet with the rows labeled Time 0, Time 1, Time 2...,
Time 1000. The columns show the output data for Option A, Option B,
.... Option Z. The cells show temperature, for example. Another summary
chart might show some other property at time 0 thru time 1000 --
chemical concentration or whatever.

Now I want to chart these outputs, but I have too many scenarios to
include them all on the charts simultaneously. Twenty lines ("series")
is too many for one chart. I might want to take a look at the
temperature curves of just Options A, D, E, and G on the charts. Then
I might want to remove G and add H and I. Then remove them all and
look at Options B vs. C.

What I'd like to do is just go to the input page and put either "yes"
or "no" at the top of each column to indicate whether I want the
charts to include the outputs from that scenario.

But I don't know how to create charts that dynamically add or remove
series depending on the value in some cell at the top of the input
columns on the input page.

I'd be grateful for any suggestions.

Hi Tuanglen,

Here are some links about creating dynamic Chart.
146055 Using Visual Basic to Create a Chart Using a Dynamic Range
http://support.microsoft.com/?id=146055
185177 XL98: How to Create a Dynamic Chart by Using Visual Basic
http://support.microsoft.com/?id=185177
193249 XL98: Using Visual Basic to Create a Chart Using a Dynamic Range
http://support.microsoft.com/?id=193249

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
From: (Tuang)
Newsgroups: microsoft.public.excel.misc,microsoft.public.excel .programming
Subject: turning lines on/off on a set of charts
Date: 24 Sep 2003 12:42:29 -0700
Organization: http://groups.google.com/
Lines: 40
Message-ID:
References:

NNTP-Posting-Host: 63.203.50.162
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1064432550 32255 127.0.0.1 (24 Sep 2003
19:42:30 GMT)
X-Complaints-To:
NNTP-Posting-Date: 24 Sep 2003 19:42:30 GMT
Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
m!newsfeed.frii.net!newsfeed.frii.net!140.99.99.19 4.MISMATCH!newsfeed1.easyn
ews.com!easynews.com!easynews!sn-xit-02!sn-xit-06!sn-xit-09!supernews.com!po
stnews1.google.com!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:418693
microsoft.public.excel.misc:275168
X-Tomcat-NG: microsoft.public.excel.programming

"dvt" wrote in message
...


I don't understand exactly what you're charting. But let me throw out a
few
suggestions.

First, you could transpose your data...

No, no. I'm sorry I wasn't clear. I have a page (meaning worksheet)
of inputs. Imagine row1 being something like "NaCl concentration",
row2 being "starting temperature", row3 being "voltage", etc., and
each column contains a different set of these parameters. There's a
column with values for a scenario called "Option A", one to its right
called "Option B", etc., each column containing some value for each of
the input parameters.

Those inputs feed into several pages of models. The models do lots of
analysis and send their outputs to several summary worksheets. Imagine
one summary worksheet with the rows labeled Time 0, Time 1, Time 2...,
Time 1000. The columns show the output data for Option A, Option B,
... Option Z. The cells show temperature, for example. Another summary
chart might show some other property at time 0 thru time 1000 --
chemical concentration or whatever.

Now I want to chart these outputs, but I have too many scenarios to
include them all on the charts simultaneously. Twenty lines ("series")
is too many for one chart. I might want to take a look at the
temperature curves of just Options A, D, E, and G on the charts. Then
I might want to remove G and add H and I. Then remove them all and
look at Options B vs. C.

What I'd like to do is just go to the input page and put either "yes"
or "no" at the top of each column to indicate whether I want the
charts to include the outputs from that scenario.

But I don't know how to create charts that dynamically add or remove
series depending on the value in some cell at the top of the input
columns on the input page.

I'd be grateful for any suggestions.

Tuang -

This page shows how to use a worksheet control (check box or combo box)
to control what series are plotted. But you basically work off a
worksheet cell containing True or False.

http://www.geocities.com/jonpeltier/...ByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Tuang wrote:
"dvt" wrote in message ...



I don't understand exactly what you're charting. But let me throw out a few
suggestions.

First, you could transpose your data...


No, no. I'm sorry I wasn't clear. I have a page (meaning worksheet)
of inputs. Imagine row1 being something like "NaCl concentration",
row2 being "starting temperature", row3 being "voltage", etc., and
each column contains a different set of these parameters. There's a
column with values for a scenario called "Option A", one to its right
called "Option B", etc., each column containing some value for each of
the input parameters.

Those inputs feed into several pages of models. The models do lots of
analysis and send their outputs to several summary worksheets. Imagine
one summary worksheet with the rows labeled Time 0, Time 1, Time 2...,
Time 1000. The columns show the output data for Option A, Option B,
.... Option Z. The cells show temperature, for example. Another summary
chart might show some other property at time 0 thru time 1000 --
chemical concentration or whatever.

Now I want to chart these outputs, but I have too many scenarios to
include them all on the charts simultaneously. Twenty lines ("series")
is too many for one chart. I might want to take a look at the
temperature curves of just Options A, D, E, and G on the charts. Then
I might want to remove G and add H and I. Then remove them all and
look at Options B vs. C.

What I'd like to do is just go to the input page and put either "yes"
or "no" at the top of each column to indicate whether I want the
charts to include the outputs from that scenario.

But I don't know how to create charts that dynamically add or remove
series depending on the value in some cell at the top of the input
columns on the input page.

I'd be grateful for any suggestions.