Can the number of series be dynamically linked to contents of a cell?

  • Thread starter Thread starter Bruce Cooley
  • Start date Start date
B

Bruce Cooley

It seems like this would be a simple thing to do, but I can't figure out
how: I want my chart to use one column of data from a table if a certain
cell has the value 1. If the cell says 2, I want the chart to use two
columns of data from the table. If the cell changes back to 1, I want the
chart to revert to the first series only.

Thanks in advance for any ideas on how to do this.

Bruce
 
I should clarify that when the cell value is 1, the bar chart should display
a single series with bars spaced as if never was any second series. In
other words, blanking the data for Series 2 won't do it because that just
appears as if the bars for Series 2 are invisible. The chart needs to think
there is only one series.


: It seems like this would be a simple thing to do, but I can't figure out
: how: I want my chart to use one column of data from a table if a certain
: cell has the value 1. If the cell says 2, I want the chart to use two
: columns of data from the table. If the cell changes back to 1, I want the
: chart to revert to the first series only.
:
: Thanks in advance for any ideas on how to do this.
:
: Bruce
:
:
 
Well, I figured out how to do this with VBA, but is there a way to do this
by using dynamic formulas in the Series Formula? That's what I couldn't
figure out.

Sub AdjustSeries()
Sheet1.ChartObjects(1).Activate
If Sheet1.Range("Compare") = 1 Then
ActiveChart.SetSourceData Source:=Sheet1.Range("B1:B7")
Else: If Sheet1.Range("Compare") = 2 Then ActiveChart.SetSourceData
Source:=Sheet1.Range("B1:C7")
End If
End Sub


: I should clarify that when the cell value is 1, the bar chart should
display
: a single series with bars spaced as if never was any second series. In
: other words, blanking the data for Series 2 won't do it because that just
: appears as if the bars for Series 2 are invisible. The chart needs to
think
: there is only one series.
:
:
: : : It seems like this would be a simple thing to do, but I can't figure out
: : how: I want my chart to use one column of data from a table if a
certain
: : cell has the value 1. If the cell says 2, I want the chart to use two
: : columns of data from the table. If the cell changes back to 1, I want
the
: : chart to revert to the first series only.
: :
: : Thanks in advance for any ideas on how to do this.
: :
: : Bruce
 
Bruce -

Unfortunately, dynamic ranges by themselves can only indicate the number of points
in a series, but not the number of series in a chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top