I have a worksheet that contains numerical data in cells F5 to something
like F20 or similar (depending on the event).

The data is refreshed from an external source (the time interval can be
adjusted by me).

Would it be possible to produce a graph below the data that would
refresh itself at an intervals of about 30-60 seconds or something set
by me, so that I could have a visual representation of events?

TIA

If you make a chart, doesn't it update with the data?

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


"Saxman" wrote in message
...
I have a worksheet that contains numerical data in cells F5 to something
like F20 or similar (depending on the event).

The data is refreshed from an external source (the time interval can be
adjusted by me).

Would it be possible to produce a graph below the data that would refresh
itself at an intervals of about 30-60 seconds or something set by me, so
that I could have a visual representation of events?

TIA

Jon Peltier wrote:
If you make a chart, doesn't it update with the data?

That's what I thought, but does it not overwrite the previous data, or
does the chart progress?

I'm asking you. I don't know the specifics of how your data is updated or
how your chart is defined.

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


"Saxman" wrote in message
...
Jon Peltier wrote:
If you make a chart, doesn't it update with the data?

That's what I thought, but does it not overwrite the previous data, or
does the chart progress?

Jon Peltier wrote:
I'm asking you. I don't know the specifics of how your data is updated or
how your chart is defined.

It is sourced from a website and put into a worksheet for me where it is
continually updated at a time set by myself.

Basically, it would be price on one axis and time on the other.

Jon Peltier wrote:
If you make a chart, doesn't it update with the data?

It certainly is dynamic and updates the data, but I would like to view
changes in the data and not to get overwritten on a line graph.

For example, time on the x axis which could be triggered by the data
refresh. On the y axis would be different sets of data (cells F5 -
F20). One could then compare which set of data was
increasing/decreasing relative to one another.

So the new data overwrites the old? You could have a routine which copies
data after a recalculation event (which occurs when the data is updated) and
stores it onto another sheet. Then you can chart these blocks of data as
separate series on a common chart.

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


"Saxman" wrote in message
...
Jon Peltier wrote:
If you make a chart, doesn't it update with the data?

It certainly is dynamic and updates the data, but I would like to view
changes in the data and not to get overwritten on a line graph.

For example, time on the x axis which could be triggered by the data
refresh. On the y axis would be different sets of data (cells F5 - F20).
One could then compare which set of data was increasing/decreasing
relative to one another.

Jon Peltier wrote:
So the new data overwrites the old?

Yes, it is always being refreshed at a rate set by the user.

You could have a routine which copies
data after a recalculation event (which occurs when the data is updated) and
stores it onto another sheet.

This could be copy/paste with a timer?

Then you can chart these blocks of data as
separate series on a common chart.

Not sure what you mean as a novice?

Thanks for the feedback. This has been an age old problem of mine. At
least you have given me a guideline. For a novice, these problems are
horrendous, as there is virtually no material available, even in decent
Excel VBA books.

I have tried using macros before doing a copy/paste into another
worksheet, but they just seem to crash.

Could you give me an example of a routine, so then maybe I could modify it?

Let's say you have two sheets, the "Data" sheet which keeps refreshing and
the "Archive" sheet where you will store old data.

You can set up an event procedure on the Data worksheet. Right click the
Data worksheet tab and choose View Code. In the module that pops up, choose
Worksheet from the left dropdown, and Calculate from the right. This code
appears:

Private Sub Worksheet_Calculate()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Delete the second pair of lines:

Private Sub Worksheet_Calculate()

End Sub

Now you need code. I don't know what your data range looks like, let's
assume A2:A11, leaving a label in A1.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False

Worksheets("Data").Range("A1:A11").Copy
Worksheets("Archive").Range("A2").Insert Shift:=xlDown

Application.EnableEvents = True
End Sub

This inserts the new data at the top of the Archive sheet, just under the
label in A1. This occurs every time there's a calculation in the sheet,
which happens when the data is refreshed. The EnableEvents commands turn off
events in case another calculation is caused by the copy/paste operations,
so you don't copy the same data numerous times.

Set up your chart by defining some names. Go to Insert menu Names
Define. In the dialog, enter a name for the first named range and a formula
to refer to it:

Name: Values1
RefersTo: =Offset(Archive!$A$1,1,0,10,1)

This means start at the cell one below and zero to the right of A1 (that's
A2), and make the range 10 rows and 1 column in size (i.e., A2:A11). This is
the data range for the first chart series. Add more series as desired (I'm
doing five for this example):

Name: Values2
RefersTo: =Offset(Archive!$A$1,11,0,10,1)
Name: Values3
RefersTo: =Offset(Archive!$A$1,21,0,10,1)
Name: Values4
RefersTo: =Offset(Archive!$A$1,31,0,10,1)
Name: Values5
RefersTo: =Offset(Archive!$A$1,41,0,10,1)

These are five named ranges which will always refer to A2:A11, A12:A21,
A22:A31, A32:A41, and A42:A51, no matter that you keep inserting more data.
The last five blocks of data will always be shown in the chart.

Now create a chart. In step 2 of the Wizard, on the Data Range tab, clear
what's in the selection box, then select the Series tab. Click Add, then
enter this in the Values box:

=Archive!Values1

which refers to the first defined name above. Click Add again and this time
enter

=Archive!Values2

and repeat for as many sets of Values as you've defined.

Now you can copy the chart and paste it onto the Data sheet (it will still
refer to the defined data on the Archive sheet), and you will see the
current and several recent sets of data.

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


"Saxman" wrote in message
...
Jon Peltier wrote:
So the new data overwrites the old?

Yes, it is always being refreshed at a rate set by the user.

You could have a routine which copies
data after a recalculation event (which occurs when the data is updated)
and stores it onto another sheet.

This could be copy/paste with a timer?

Then you can chart these blocks of data as
separate series on a common chart.

Not sure what you mean as a novice?

Thanks for the feedback. This has been an age old problem of mine. At
least you have given me a guideline. For a novice, these problems are
horrendous, as there is virtually no material available, even in decent
Excel VBA books.

I have tried using macros before doing a copy/paste into another
worksheet, but they just seem to crash.

Could you give me an example of a routine, so then maybe I could modify
it?