Charting data from a changing linked cell over time

[elguapo23]

Hello,

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).

Is there a way to do this?

Thank you for any help you can offer.

 

[Mala]

The chart should auto-update every time the data in the cell changes - whether through an external link or user change.
Please post more details.

 

[elguapo23]

Thanks for your reply.

Let me clarify that I have only a single cell with its value changing sporadically. What I would like to do is chart the different levels of this one cell as it changes. For example, the value of the cell (linked from an external application) is 1.5, in 3 minutes it is -.25, in another 1 minute it is -.58. I don't have a column with this data as it changes, just the one cell with the current value. Do I need to somehow make another sheet that this data can be sent to when it changes or on some time interval and then chart that? If so how can this be done?

Any help is much appreciated,

 

[Mala]

Let's say the changing cell is Sheet1!A1
We will store the changing data in Sheet2 in column A.

Enter the following code in the Worksheet module for Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        MyVal = Range("A1").Value
        Sheet2.Cells(Me.Rows.Count, 1).End(xlUp)(2, 1) = MyVal
    End If
End Sub

In Sheet2 enter a heading (say 'Data') in A1

Define a dynamic name 'ChtData' for the data range in Sheet2:
ChtData refers to:
=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

Let's say the workbook name is Book 2.xls

Create a line chart with some dummy data and edit the series formula in the formula bar so it looks like:
=SERIES(,,Sheet2!ChtData,1) - click the tick mark to the left of the formula bar to accept.
You will notice that the Sheet reference changes to the workbook reference:
=SERIES(,,'Book 2.xls'!ChtData,1)

Form now on the chart will track the entire history of the changing cell

 

[elguapo23]

Thanks for your reply.

If I have several different cells on the sheet (with different variables) that I want to create columns for so that they can be charted how can I do that? Also, is it possible to include the timestamp with the data on sheet2 so the variable can be charted over time. This might also help to chart more than 1 variable on the same chart.

Thanks for your help, it is very knowledgeable.

 

[elguapo23]

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]

Also, I crossposted this at:
http://www.vbaexpress.com/forum/showthread.php?p=165829

 

[tigertiger]

Thanks for your reply.

If I have several different cells on the sheet (with different variables) that I want to create columns for so that they can be charted how can I do that? Also, is it possible to include the timestamp with the data on sheet2 so the variable can be charted over time. This might also help to chart more than 1 variable on the same chart.

Thanks for your help, it is very knowledgeable.

YES, U also do as Mala's help

U should pay attention to the code (I marked in red color)


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
MyVal = Range("A1").Value
Sheet2.Cells(Me.Rows.Count, 1).End(xlUp)(2, 1) = MyVal
End If
End Sub


and

You can copy the statements to other case in the Sub, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
MyVal = Range("A1").Value
Sheet2.Cells(Me.Rows.Count, 1).End(xlUp)(2, 1) = MyVal
End If

If Target.Address = "$C$1" Then
MyVal = Range("C1").Value
Sheet2.Cells(Me.Rows.Count, 2).End(xlUp)(2, 2) = MyVal
End If

End Sub

 

[elguapo23]

Thanks for the reply tigertiger.

I see how this would work with regard to creating multiple columns.


Do you have any insight into why the values are not updating in the new column when the value in the target cell changes via the link. It will add them when I manually enter values but not when the displayed value changes through the formula. Any help here is appreciated.

Thanks.

 

[Mala]

Tigertiger,
Thank you, you have explained it very well.

elguapo23,
The change event is not triggered by formula-based changes.
Date-time stamp - in the code use two columns instead of one - first column for date-time and the other for value. And make the time stamp the x-values of the chart series.