Creating a Macro which allows me to change the data series of a ch

[Cath]

Scenario : I have a large data table, possibly 100 cols wide and 100 rows
deep. I have created a graph built from one column worth of data. Can I
create a macro which when used will move the source of the data to the
nextcolumn, with this being carried out repeatedly.

I only want to display one col at a time in the graph. I would like to have
two buttons - one for moving to the next column and one to move back to the
previous column. Is this possible ?

 

[Bernard Liengme]

You do not need a macro
Suppose your data is in A1:ZZ100
Insert a new row 1 and a new column B
In A1 enter the text Column, in B1 enter the column to be plotted (say V)
In B2 enter =INDIRECT($B$2$ROW()) and copy down the column by double
clicking the fill handle
Now use columns A and B to make chart
Change enter in B1 to get different data.

If you would rather use numbers in B1, say 3 for the data in D, then modify
the above to
=INDIRECT(CHAR($B$1+65)&ROW())

best wishes

 

[Andy Pope]

Hi,

No need for a macro.

You could use formula, such as INDEX, to display values of chosen column.
Then create a chart on this fixed set of data.

You could use a Spinner control to determine which of the 100 columns you
what displayed.

Cheers
Andy

 

[Bernard Liengme]

Much more creative than my answer, Andy!
But I do have a bad case of 'flu
best wishes
--
Bernard

Hi,

No need for a macro.

You could use formula, such as INDEX, to display values of chosen column.
Then create a chart on this fixed set of data.

You could use a Spinner control to determine which of the 100 columns you
what displayed.

Cheers
Andy

 

[Cath]

Thanks works very well!
--
Thanks
Cath

You do not need a macro
Suppose your data is in A1:ZZ100
Insert a new row 1 and a new column B
In A1 enter the text Column, in B1 enter the column to be plotted (say V)
In B2 enter =INDIRECT($B$2$ROW()) and copy down the column by double
clicking the fill handle
Now use columns A and B to make chart
Change enter in B1 to get different data.

If you would rather use numbers in B1, say 3 for the data in D, then modify
the above to
=INDIRECT(CHAR($B$1+65)&ROW())

best wishes

 

[Andy Pope]

Thanks' Bernard, but we were both 'singing' from the same sheet on this one


Hope your bout of flu is a short one.

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Much more creative than my answer, Andy!
But I do have a bad case of 'flu
best wishes

 

[Cath]

Many Thanks