Conditional Chart Range?

[McGu]

Is there a way to chart a different range of cells based on a condition?

For example:

If "yes" then plot A1:H1

If "no" then plot A2:H2

To my knowledge, the chart axis won't accept an IF statement.

 

[tactps]

You can use a VBA event handler:

Press Alt-F11 to open VBA
Double-click the sheet that you want to attach this to.

Paste this into the right pane:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$4" And LCase(Target.Text) = "yes" Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:H1"), PlotBy:= _
xlRows
Else
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:H2"), PlotBy:= _
xlRows
End If
End Sub

This assumes that you have one chart called "Chart 1", that the sheet is "Sheet1" and that the word "yes" will appear in cell A4 when you want to plot A1:H1. You do not need the word "no" to plot the other range.

Hope that this helps

 

[McGu]

Hey thanks, I'll try it out and let you know how it works for me. I didn't know it would be that complicated, but what do I know.

 

[tactps]

As you say:
"the chart axis won't accept an IF statement"
so we need to look to other methods.

In my VBA, all it does is looks at cell A4 and if it sees the word "yes" it changes the source data to the new range.

 

[McGu]

I'm trying to follow your instructions, but am a little unclear. Please forgive my VBA inexperience. I just bought a book on it.

1. I already have the sheet named 'Revenue' selected.
2. I hit Alt + F11

3. You sau to 'Paste into the right pane', but there is nothing there but grey space.

 

[McGu]

I selected Insert>module. Is this correct? I'll try it out after I save. Ha!

 

[McGu]

Here is what I have. I modified the code slightly to fit my needs, but am unclear about where to find the actual name of the chart that you refer to as "Chart 1".

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "ControlSheet!$C$11" And LCase(Target.Text) = "Yes" Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Revenue").Range("Revenue!$B$27:$E$27,Revenue!$G$27:$K$27"), PlotBy:= _
xlRows
Else
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Revenue").Range("Revenue!$B$27:$F$27,Revenue!$H$27:$K$27"), PlotBy:= _
xlRows
End If
End Sub

 

[tusharm]

Yes, a chart does not accept a formula. But, it accepts a named formula.

Suppose you have the "yes" value in A5. Then, create a named formula (Insert | Define > Name...)

YRng =IF(Sheet1!$A$5="yes",Sheet1!$A$1:$H$1,Sheet1!$A$2:$H$2)

Now, use YRng as the y-values in the chart series. Your SERIES formula should look like
=SERIES(,,Book2!YRng,1)

For more on using named formulas in charts see
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
particularly the links in the first paragraph.

Is there a way to chart a different range of cells based on a condition?

For example:

If "yes" then plot A1:H1

If "no" then plot A2:H2

To my knowledge, the chart axis won't accept an IF statement.

 

[McGu]

Great! I think I'll go that route instead of the VBA just for ease. Thanks again for the fast responses and the time you guys put in.