Multiple Source Scrolling Chart (horizontal)

**omarq** · 08-11-2011, 04:24 AM

Hi,

I'm trying to makea scrolling chart that shows multiiple sources on the same chart. Each is on the same line and I would like to be able to scroll through on a line by line basis.

I'm a little stuck here. I have attached an example of what I am trying to do however am very new to this type of charting. Please can someone help here?

Thanks in advance!!

**teylyn** · 08-11-2011, 04:38 AM

Hello,

first, get rid of the merged cells.

Define three range names with these formulas:

chtSeriesX =OFFSET(Sheet1!$C$2:$G$2,Sheet1!$U$3,0)
chtSeriesY =OFFSET(Sheet1!$I$2:$M$2,Sheet1!$U$3,0)
chtSeriesZ =OFFSET(Sheet1!$O$2:$S$2,Sheet1!$U$3,0)

Edit the chart and specify the data source for each series, using the range names you just defined. For example, data series X

Series name =Sheet1!$C$2
Series values =Sheet1!chtSeriesX

Repeat for the Y and Z series.

Now, when you alternate the number 1, 2 and 3 in cell U3, the chart will change.

See attached.

For more information about dynamic charts, have a look at Jon Peltier's site, especially http://peltiertech.com/Excel/Charts/Dynamics.html

cheers,

**omarq** · 08-11-2011, 05:06 AM

Thanks!! is there a way to have the chart title also to be dynamic, so it will change respective to the data?

**teylyn** · 08-11-2011, 05:38 AM

Yes.

One way is to create a cell in the worksheet that holds the text for the chart title. Use a formula to create the correct text based on the number the user selects in U3 (referring to the sample file attached above).

Click the chart title, then click the formula bar, enter a = sign and click the cell with the prepared text. Now, when the selection in U3 changes, the chart title will change as well.

cheers,

**omarq** · 08-11-2011, 06:56 AM

Thanks for the info... i'm not quite sure about how to construct the forumla based off the number to reference the colums, (e.g. a, b, or c.) Would you be able to provide some more detail here?