Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How To Anchor Chart Data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How To Anchor Chart Data
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 _______ "DaveM" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How To Anchor Chart Data
THat did it Jon. Thx a lot.
"Jon Peltier" wrote: 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 _______ "DaveM" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HTML Anchor tag? (name tag)... | Excel Discussion (Misc queries) | |||
Anchor Doesn't work | Excel Worksheet Functions | |||
How do I anchor a cell reference? | Excel Discussion (Misc queries) | |||
How do I anchor adjacent cells ? | New Users to Excel | |||
How do I anchor one end of a linear trend line? | Charts and Charting in Excel |