[Peter Lyons]

Afternoon everyone,

I have created a number of charts that plot information across 26 weeks and 4 data series'.

The issue I have is that I would like to create a macro or command to display one of the series at a time in the same graph. If possible all combinations so any two series can be compared against each other. just it can seem messy to some of the viewers if they are not 100% about what they are looking at.

Is this possible and if so how is it best done as all attempts I have had so far at recording a macro have come to nought?

Many thanks in advance,

Pete

[Derek Christie]

On May 2, 2:02*am, Peter Lyons
wrote:
Afternoon everyone,

I have created a number of charts that plot information across 26 weeks
and 4 data series'.

The issue I have is that I would like to create a macro or command to
display one of the series at a time in the same graph. If possible all
combinations so any two series can be compared against each other. just
it can seem messy to some of the viewers if they are not 100% about what
they are looking at.

Is this possible and if so how is it best done as all attempts I have
had so far at recording a macro have come to nought?

Many thanks in advance,

Pete

--
Peter Lyons

It's not quite clear what you want, Pete. if you want to be able to
choose combinations of two series and put them on the same graph, one
way is to make a set of two dummy variables to hold the series you
want and draw the graph of them. Then you can use two drop down boxes
to select the series you want and use OFFSET to copy the picked series
into the dummy positions.

[Peter Lyons]

It's not quite clear what you want, Pete. if you want to be able to
choose combinations of two series and put them on the same graph, one
way is to make a set of two dummy variables to hold the series you
want and draw the graph of them. Then you can use two drop down boxes
to select the series you want and use OFFSET to copy the picked series
into the dummy positions.

Derek,

Thanks for the reply and hope that this clarifies the question a bit.

As they say a picture paints 1000 words. I have attached a sample chart that I am using.

As you can see the 3 lines are quite close and if tracking a single shift can get confusing.

So If I wanted to look purely at "A Shift" I would normally have to create a new chart.

Surely there is a way I can use this chart but have a selection box or button for each of the shifts.

Haven't used the OFFSET command before I was trying to set a macro to do it but coudn't get it working.

Many thanks inadvance,

Peter Lyons

[[email protected]]

On Wednesday, May 2, 2012 2:02:51 AM UTC+12, Peter Lyons wrote:
Afternoon everyone,

I have created a number of charts that plot information across 26 weeks
and 4 data series'.

The issue I have is that I would like to create a macro or command to
display one of the series at a time in the same graph. If possible all
combinations so any two series can be compared against each other. just
it can seem messy to some of the viewers if they are not 100% about what
they are looking at.

Is this possible and if so how is it best done as all attempts I have
had so far at recording a macro have come to nought?

Many thanks in advance,

Pete




--
Peter Lyons

Another way which sounds like what you want is to have your data with a toggle button above each column which you click if you want that series to appear. You can do this by actually graphing a dummy series. Say that the real data is in in B3:B10 and you have a toggle button which puts a TRUE into B1 if you want the series to be plotted (otherwise a FALSE). Put the dummy data over in say F3:F10. F3 reads =if($B$1,B3,#n/a) and copy this down.. This is the data you plot. If B1 = TRUE then the graph is visible. if B1 = FALSE the the graph disappears. Repeat for the other columns.
To put a toggle button in for Excel 2007, you will need to go into options and get the Developer pane visible. It probably already is if you are writing macros. Then Developer - Controls - Insert - ActiveX - Toggle Button - drag and outline - right click - Properties - Linked Cell B1 -
Design Mode off.

Or, send me a copy of the sheet as it is to the.christies at clear.net.nz and I'll give you an example.

D