Dynamic line charting of multiple series of data

[rickcb]

I have a worksheet with 180 columns of data (17565 rows of data). I have to create a chart with each column. I'm looking for a way to do this dynamically instead of creating 180 line charts.

Help anyone !

 

[Jon Peltier]

You can define a dynamic chart based on named ranges. To name a range, go to the Insert menu > Names > Define. In the Name box, enter the name, and in the Refers To box, enter the formula that defines the range.

So your data starts in A1, is 180 columns wide and 17565 rows high, in Sheet 1 of Book1.xls. In Sheet 2 cell B2, enter a value between 1 and 180.

Now to define the range:

Insert > Names > Define

Name: MyData
RefersTo:
=OFFSET(Sheet1!$A$1:$A$17565,0,Sheet2!$B$2-1)

Create a chart with the first column. Select the series, and the formula bar should show this:

=SERIES(,,Sheet1!$A$17565,1)

Change this to read

=SERIES(,,Sheet1!MyData,1)

Press Enter, and Excel will change it to:

=SERIES(,,Book1.xls!MyData,1)

Change the value in Sheet2!B2 and the chart will reflect the data in the indicated column. You can make this more fun using a scroll bar linked to Sheet2!B2.

More on Dynamic Charts:
http://peltiertech.com/Excel/Charts/Dynamics.html