Chart select, yet changing set of series

  • Thread starter Thread starter name
  • Start date Start date
N

name

How can I have a chart that *only* plots series chosen by the user. Fo
example:

I would like to have a on/off (1/0) switch for each series of data.
want the chart to only plot series with the on switch value. Also, I'
looking for a true solution, not one that plots the off series on th
axis.

All help is, as always, greatly appreciated
 
Oh I forgot to ask whether or not there's a way to setup a switch that
will flip through different chart types.
 
Hi:
Your first question is relatively easy to do, with the IF command. Make a
copy of yr series (so you have two of each, X and Y). X is yr basic data,
and Y is calculated depending on a value in yr "trigger" cell (say this is
cell A1, and the value is 0 or 1).
Series Y is then for instance IF(A1=1,"X",NA()). Now plot Series Y, not X.
It either shows the values (if A1=1) or nothing (the effect of the NA()
command.

Your second question, the "chart flip": I think it is possible with the help
of the CHOOSE command, but frankly I don't see how this can be a time-saver -
the only sensible reason for this. I use CHOOSE when I want to change my
chart's appearance triggered by an event or value (e.g. change the chart area
into red for alerting purposes).
Just in case you are not aware: you don't have to remake a chart from
scratch once you have one: you can always right click the existing chart and
choose a different Chart Type and .. voila. Having said this, you may wish
to see my answer on the post of Rich earlier today in case you use
time-consuming chart formats that you don't like to make over again each
time.
HTH,
GL,
Henk
 
The solution offered by Kelly is fantastic but this may be easier.

from forms toolbox
The checkbox is linked to cell c4,etc
Just add more for each column that needs to be involved.
right click sheet tab>view code>insert this.
Private Sub Worksheet_calculate()
'put =now()somewhere on the sheet of the chart
With Sheets("data")
.Columns.Hidden = False
If Range("c4") = False Then .Columns(2).Hidden = True
If Range("c5") = False Then .Columns(3).Hidden = True
End With
End Sub

from control box
Private Sub CheckBox1_Click()
If CheckBox1.Value = False Then _
Sheets("data").Columns(2).Hidden = True
End Sub
 
Back
Top