Using conditional statements to select chart data

M

Mark440

New Member
Joined
Aug 1, 2009
Messages
37
Trying to figure out how to make the chart data range selective. I have a single pie chart, yet I have the need to selectively display two different data ranges.
Range 1 is C5:D15
Range 2 is AA50:AB55

For both ranges, the data present is derived from formulas.

I'd like to use a couple option buttons to choose which data actually gets displayed in the chart.

I named the two data ranges, and grouped the option buttons. But when I tried to use an IF statement in the chart's data selection - I get 'not a valid function'.

Thanks for any help!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
No, you'll have to do this via VBA. First link your option buttons to a cell somewhere, say X1, then in X2 place =X1+0: this will ensure that when X1 changes, the Worksheet_Calculate event is triggered. (Clicking an option button alone won't do this.)

Now open the code window for the worksheet by right-clicking the sheet's tab and selecting View Code. In the code window paste the following VBA:-
Code: 
Private Sub Worksheet_Calculate()
 
  Sheets("[COLOR=red][B]Sheet1[/B][/COLOR]").ChartObjects("[COLOR=red][B]Chart 1[/B][/COLOR]").Activate
  If Range("[B][COLOR=red]X1[/COLOR][/B]") = 1 Then
     ActiveChart.SetSourceData Source:=Range("[B][COLOR=red]A1:A10[/COLOR][/B]")
   Else
    ActiveChart.SetSourceData Source:=Range("[COLOR=red][B]B1:B10[/B][/COLOR]")
   End If
 
End Sub
Change the bits in red to suit. Every time an option button gets clicked, the chart should change. Shout if it doesn't!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Custom Chart template when using slicer
Replies
2
Views
115
RodneyW
R
O
  • Question
Creating a Graph/Chart needing TWO secondary axis options for a combination of Clustered and Stacked Graph Columns
Replies
0
Views
303
OrangeJuice
O
A
  • Question
Series Chart and Data with IF Statement
Replies
2
Views
263
NdNoviceHlp
NdNoviceHlp
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
130
Nick Loughton
N
P
  • Question
Pivot Chart - Displaying too much data
Replies
1
Views
281
alansidman
A

Forum statistics

Threads
1,214,591
Messages
6,120,424
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top