You are here:

Excel/Plotting data generated by Excel

Advertisement


Question
QUESTION: Greetings,

I have a challenge within Excel that I would like assistance with.

On one tab I am keeping a running total of documents reviewed (my application), which has a single cell representing the total number of documents reviewed, which changes as documents are reviewed...

On a second tab I've created a line graph showing 'Running Total' which ideally should represent the total number of documents reviewed.

My problem is that I do not know how to stream data out of the 'total documents reviewed' cell into the plotted graph on the second tab, and keep all the plotted data in the plotted chart...

Ideas?

ANSWER: It sounds as though you periodically need to take a snapshot of the amounts received?  Otherwise you are graphing a single cell, which doesn't sound right?  Do you have any macros currently running, and if not do you have any experience of macros?  The non macro solution would be to have a dynamic range for the data source for your chart, and add new data to it by copying and pasting as you go.

---------- FOLLOW-UP ----------

QUESTION: Let me rephrase the need I have...

I have a single cell representing a count which is continuously being updated with new count data, ex:  3, 67, 98, etc.

I want this single cell to link to a live plot in a graph, such that as the total count data in the cell increases, the plot adds that value to the plotted line, while retaining the old plotted line....

ANSWER: My answer pretty much holds - everytime the data changes, it needs to be added to an expanding list of data points so that the graph can update - how is the data currently updated (do you have macros running? - if so, the macro needs amending). If it's being fed from amother source, then PROBABLY the sheet change event would be triggered so can be used.

---------- FOLLOW-UP ----------

QUESTION: Thanks for your patience - I believe we are making progress...

You suggest that I can get what I want, streaming output from a cell directly into a plot/chart, while not losing 'old' data for the plot/chart, but using a Macro.

I am not using a Macro currently.

I'm also not familiar with Macros - what do you suggest?

Answer
something along these lines

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
   Sheets("sheet2").Range("A" & WorksheetFunction.CountA(Sheets("Sheet2").Range("A:A")) + 1).Value = Target.Value
End If
End Sub


In this instance, whenever A1 on the active sheet is updated, the new value is added to the bottom of the range of cells in column A on sheet2.  It would need tidying up a bit, if only to refresh the graph data!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.