Charting data from a changing linked cell over time

M

Mala

MrExcel MVP

Joined

Apr 4, 2002

Messages

627

• Nov 7, 2008

• #2

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.

 

Upvote 0

E

elguapo23

New Member

Joined

Nov 6, 2008

Messages

8

• Nov 7, 2008

• #3

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,

 

Upvote 0

M

Mala

MrExcel MVP

Joined

Apr 4, 2002

Messages

627

• Nov 7, 2008

• #4

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

Code:

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

 

Upvote 0

E

elguapo23

New Member

Joined

Nov 6, 2008

Messages

8

• Nov 9, 2008

• #5

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.

 

Upvote 0

E

elguapo23

New Member

Joined

Nov 6, 2008

Messages

8

• Nov 9, 2008

• #6

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.

 

Upvote 0

E

elguapo23

New Member

Joined

Nov 6, 2008

Messages

8

• Nov 9, 2008

• #7

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

 

Upvote 0

T

tigertiger

New Member

Joined

Nov 9, 2008

Messages

46

• Nov 9, 2008

• #8

elguapo23 said:

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.

Click to expand...

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

 

Upvote 0

E

elguapo23

New Member

Joined

Nov 6, 2008

Messages

8

• Nov 10, 2008

• #9

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.

 

Upvote 0

M

Mala

MrExcel MVP

Joined

Apr 4, 2002

Messages

627

• Nov 10, 2008

• #10

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.

 

Upvote 0