help: Use a radio button to select Chart type

[MartinL]

I have a couple of users who argue about the type of chart they want to see.
One wants a pie the other a stacked bar.
personally I'd always use a stacked bar, but as the pie guy is way abve my pay grade and anargumentative so and so I have no option but to do both.

The Idea I posed to them which they have agreed to is that They can select the chart style themselves on a KPI worksheet by clicking on a radio button.

So far I have the two charts built and the Radio button ready so a cell reads 1 or 2 depening on which is selected.

Currently all my charts are copy and paste linked in to the KPI worksheet so that when all the charts are ready, they all appear as a single dashboard (KPI) Sheet.

How can I get the chart to display based on the radio button selection.

Dashboard is on Worksheet KPI
Both Charts are on Worksheet CH7
Radio Button is on Worksheet CH7
and the control value is on Worksheet CH7 cell G2

Any ideas.

 

[mrhopko]

you could acheive this by using VBA to create and format the chart appropriately on your Dashboard and triggering the VBA when someone tunes in on the radio (assuming they weren't after the shipping forecast).

 

[MartinL]

Hmm

What I've try to do now is using Name Manager create a name ("GetChart") with a formula attached to it
=IF('CH3'!$G$2=1,INDIRECT('CH3'!$F$41),INDIRECT('CH3'!$F$42))
where each chart occupies 1 cell
the cell is H255 W64
I've copied 'CH3'!$F$41 and paste link into the dashboard
Right clicked on the dashboard picture and in the formula bar put =GetChart

Excel displays the GetChart name but when I press enter in the formula bar I get the error message "Reference is not valid"

any ideas

 

[MartinL]

Suprisingly if I click on the linked picture and the formula says ='CH3'!$F$41 and I put ='CH3'!$F$42 the chart changes
But I cant seem to put an If statement in there....
Ughhh, pulling my hair out here!!