Macro to call different sheets...

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

Hi All,

I have a spready that charts a number of series of data from two different
worksheets.

The data appears on each sheet in exactly the same locations, ie.
(=Rolling!$D$9:$AM$9) for one sheet then to chart another
(=Monthly!$D$9:$AM$9).

I'd like to be able to 'switch' between these two sets of data on one chart
by way of a button on the chart, that allows me toggle the ranges,
effectively toggling the Rolling! to Monthly! and so on...

Named ranges are possible, however with 8 charts each with 5 series
represented, I'm not keen to do it this way if I can possibly help it.

Any ideas ??

Thanks,

Neil
 
Hi Neil
IMHO macros won't be easier. I'd used named ranges in combination with
INDIRECT. So you only toggle the sheet name and the referenced ranged
is changed accordingly
So you have to setup the names once but after this it should go smootly
 
Hi Neil,

Use a helper sheet to hold the chart information, with formula to select
the appropriate content from the Monthly and Rolling sheets.

ie. chart data is always in (=ChartData!D9:AM9)

and an example formula in D9 would be,

=IF(A1="Monthly",Monthly!D9,Rolling!D9)

where A1 contains the period type.

Cheers
Andy
 
Thanks Guys,

Neil


Andy Pope said:
Hi Neil,

Use a helper sheet to hold the chart information, with formula to select
the appropriate content from the Monthly and Rolling sheets.

ie. chart data is always in (=ChartData!D9:AM9)

and an example formula in D9 would be,

=IF(A1="Monthly",Monthly!D9,Rolling!D9)

where A1 contains the period type.

Cheers
Andy
Click to expand...
 
Back
Top