show / hide chart data

[Guest]

How do I add a button to hide/show a data series in my log or semi log chart.

 

[Jon Peltier]

You could hide the row/column that contains data for that series. Record a
macro while doing it manually to get the code.

You could also try one of these techniques:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon

 

[Guest]

Thanks Jon, I'm working on your exapmles.
Now that I see your reply, I have to ask another question: I am plotting the
average of the data points over my raw data (to show a smoothed 'trend'
curve), so will I be able to show this when the data series is hidden?
I'd like to hide the raw data and show the smoothed, to summarize.
Thanks, Jim.

 

[Jon Peltier]

Jim -

If you set up another range with the smoothed data, and don't hide it like
the other series, it will appear in the chart. Charting one series is
independent of the others. Not charting a series doesn't make the smoothing
calculations not work.

- Jon

 

[Guest]

That is excellent. You are an excellent resource! I found your web site very
useful.
Finally, can you explain the references in the formula you suggest
"=IF(C$3,C6,NA())" so I can understand and apply to my data a bit more
intuitively as my columns don't line up the same. Is it a simple matter to
copy the buttons to the chart worksheet, while the data remains on another?
-Jim-

 

[Jon Peltier]

C$3 contains a T/F for column C; without the $ in front of C, when you copy
the cell to another column, say one column to the right, the formula becomes
=IF(D$3,D6,NA()). You have to make adjustments for the shape of your data,
of course. The formula looks at the T/F, and if it's true it shows the value
from cell C6, if it's false it shows #N/A, which will not be plotted in a
line or XY chart. The 6 in C6 has no $ in front, so if you copy the cell
down a row it will change to C7. Check for a web page that describes
relative and absolute references, like Chip's for example:

http://cpearson.com/excel/relative.htm

You should be able to copy the controls to the chart sheet. You can only use
Forms toolbar controls on a chart, not the Controls Toolbox (ActiveX) ones.

- Jon

 

[Guest]

Well, it works nicely but the smoothed data follows the show / hide check box
function. For some reason they are not independant. Can I send you the sheet,
or some more of the cells?

-Jim-