PDA

View Full Version : Charting data from a changing linked cell over time



elguapo23
11-07-2008, 10:13 AM
I am trying to chart data from a single cell that is changing over time. The cell is linked to another software program that provides its value. I would like to be able to chart the changes in its value either every time it changes or on some time interval (i.e. every 3 minutes).

I was thinking that I might need to copy the data from the cell that changes to a new sheet every time it changes or on some time interval. However I dont know how to do this.



Thank you for any help you can offer.

Bob Phillips
11-07-2008, 11:48 AM
You are right, you need to capture the values.

This code saves the values and creates a name called dynaRange that you can use in the chart



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit
Dim NextRow As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If Worksheets("Sheet2").Range("A1").Value = "" Then

Worksheets("Sheet2").Range("A1").Value = "Values"
NextRow = 2
Else

NextRow = Worksheets("Sheet2").Range("A1").End(xlDown).Row + 1
End If

Worksheets("Sheet2").Cells(NextRow, "A").Value = .Value
ActiveWorkbook.Names.Add Name:="dynaRange", RefersToR1C1:= _
"=OFFSET(Sheet2!R1C1,0,0,COUNTA(Sheet2!C1),1)"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

RonMcK
11-07-2008, 12:01 PM
Bob,

Wouldn't it be helpful to also capture the date/timestamp for each change on the target worksheet?

Cheers,

Bob Phillips
11-07-2008, 12:05 PM
You are rright, to chart over time would help I guess :-)

elguapo23
11-07-2008, 07:54 PM
Thanks for the replies. I think this will work for me. It would be helpful to record the timestamp for each change as well. Is that possible? Also if I have more than one cell that I would like to chart, can that be done?

Also, when I create the chart how do I chart the dynaRange name so the chart keeps updating.


Thanks you for the help.

Bob Phillips
11-08-2008, 02:53 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit
Dim NextRow As Long

On Error Goto ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If Worksheets("Sheet2").Range("A1").Value = "" Then

Worksheets("Sheet2").Range("A1").Value = "Values"
NextRow = 2
Else

NextRow = Worksheets("Sheet2").Range("A1").End(xlDown).Row + 1
End If

Worksheets("Sheet2").Cells(NextRow, "A").Value = .Value
Worksheets("Sheet2").Cells(NextRow, "B").Value = Now
ActiveWorkbook.Names.Add Name:="dynaRange", RefersToR1C1:= _
"=OFFSET(Sheet2!R1C1,0,0,COUNTA(Sheet2!C1),2)"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


dynaRange is dynamic, so if you chart using that range, the chart should update as the data expabds.

When you say more than one cell, will that be separte columns in the results, or all append to one column?

elguapo23
11-08-2008, 01:34 PM
Thanks, I see how the chart will update automatically.

For the other cell; separate columns in the results. The other cell is a different variable. Is it possible to do this with a timestamp or some other means so that both columns can be charted on the same chart?

Thanks for the help.

Also, I crossposted this topic on excelforum. I can't post links however with my low post count.

elguapo23
11-09-2008, 09:07 PM
Update:

I have tried using this code and it works when I type new values into the cell. Every time I enter a new value, it updates the column in Sheet2. However, when the value in the cell is linked, and changing automatically, it does not record the values in the Sheet2 column. Any idea why this could be?

The formula in the cell looks like this:

=+D8-C7*($C$2/$D$2)

And the cells referred to there are the ones linked from the external application. The resulting value is the one I want to chart.

elguapo23
11-09-2008, 09:09 PM
Thanks for your help.

The link to Xpost is:

http://www.mrexcel.com/forum/showthread.php?p=1740654

elguapo23
11-10-2008, 11:25 PM
My issue is almost solved. The only problem I am still having is that the code I have used is not recognizing the updated value that occurs in the linked cell when it is changed via formula.

Any help on getting this to work would be great.

Thanks.