How To Anchor Chart Data

[Guest]

I am using this code to add/paste data from another sheet(1) in to column K
on sheet2 at 1 minute inertvals. This macro works fine and when it runs it
emulates a stream of data (1 min appart) which places the most current value
in colum K and the value streams to the right with the furthest right colund
being the oldest/first data value.

This acts as a ticker tape essentially while the data stream is placed in a
chart as a data series. The problem I have is I only want to plot the data
values from K1:X1 on sheet2 but i need the data stream to continue out to
column IV. Right now the chart's Y scale continues to adjust/expand as the
values grow to the right, past X1. How can I anchor the Chart's scale so it
does not expand when the insert function moves the columns to the right?

Sub UpDateSub()
Worksheets("Sheet2").Range("K1:K42").Select
Selection.Insert Shift:=xlToRight
Worksheets("Sheet1").Range("A40").Copy
Worksheets("Sheet2").Range("K1").PasteSpecial xlValues
'Schedule next update
mdNextTime = Now + TimeValue("00:01:00")
Application.OnTime mdNextTime, "UpdateSub"
End Sub

Thanks DaveM

 

[Jon Peltier]

How about defining a Name?

Go to Insert menu > Names > Define.

Enter "Sheet2!ChartData" in the "Name" box.
Enter this formula in the "Refers To" box:
"=OFFSET(Sheet2!$J$1,0,1,1,14)"

Select the chart series, look at the SERIES formula in the formula bar. It
probably looks like:

=SERIES(,,Sheet2!$K$1:$X$1,1)

Change it to read:

=SERIES(,,Sheet2!ChartData,1)

Since cell J1 isn't affected by the import. the OFFSET formula always refers
to K1:X1, and the chart always points to this range.

Usually Names are used to make charts more dynamic, but you're using it here
to point to the same static range regardless of the dynamics of the data.
More on using Names in chart source data:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[Guest]

THat did it Jon. Thx a lot.

How about defining a Name?

Go to Insert menu > Names > Define.

Enter "Sheet2!ChartData" in the "Name" box.
Enter this formula in the "Refers To" box:
"=OFFSET(Sheet2!$J$1,0,1,1,14)"

Select the chart series, look at the SERIES formula in the formula bar. It
probably looks like:

=SERIES(,,Sheet2!$K$1:$X$1,1)

Change it to read:

=SERIES(,,Sheet2!ChartData,1)

Since cell J1 isn't affected by the import. the OFFSET formula always refers
to K1:X1, and the chart always points to this range.

Usually Names are used to make charts more dynamic, but you're using it here
to point to the same static range regardless of the dynamics of the data.
More on using Names in chart source data:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______