Thread: automatically plot points on chart

I have files that contain measurment information that has rows in the
following format:

Date Data_Collector Measurement_A Measurement_B Measurement_A

where the measurements are taken at different locations.

There are multiple rows for different dates and data collectors and the
amount of information in the file varies by how many measurements were taken
in each given stretch of time.

My question is, is there a way for me to have Excel automatically plot all
of the data in the file? I would like different charts for the different
measurements, where the x-value is the date, the y-value is the measurement
value, and there are different series for each data collector.

I have been doing this by selecting the data myself, but that gets to be
very time-consuming.

Thank you.

The simple answer to your questions is Yes if you know VBA.

With a combination of dynamic range names, advanced filtering and VBA,you
can create macros to generate new charts each time period.

How are your VBA skills?

I suspect you want to know how. That is a little more involved.

The first item is your data organization. Data organization is the most
important factor in automatic generation of multiple chars. you will need
to loop through your data to generate charts by measurement and series by
data-collector. Your example data set shows three measurements, you don't
give any indication of the number of data_collectors. More than 3-5 series
per chart makes it hard to interpret series data.

Also, you don't indicate how many files you are dealing with and the sheets
per file.

For the chart time period, do you want to be able to plot just the last X
days of data, all data or user specified period?

I have an example workbook that makes 8 charts on 1 sheet. Each chart only
has one series. it may give you some ideas on how to proceed.

Let me know if you need any follow-up help.

http://processtrends.com/pg_charts_s..._XY_Charts.htm

....Kelly

koday@processtrends.com






"QuantumLeap" <QuantumLeap@discussions.microsoft.com> wrote in message
news:875E9931-78C9-46E5-A9BA-EB3CB044EFCD@microsoft.com...
>I have files that contain measurment information that has rows in the
> following format:
>
> Date Data_Collector Measurement_A Measurement_B Measurement_A
>
> where the measurements are taken at different locations.
>
> There are multiple rows for different dates and data collectors and the
> amount of information in the file varies by how many measurements were
> taken
> in each given stretch of time.
>
> My question is, is there a way for me to have Excel automatically plot all
> of the data in the file? I would like different charts for the different
> measurements, where the x-value is the date, the y-value is the
> measurement
> value, and there are different series for each data collector.
>
> I have been doing this by selecting the data myself, but that gets to be
> very time-consuming.
>
> Thank you.
>

If the data is contiguous, you can select a single cell in the range, and
Excel will expand the selection until it comes to empty rows and columns. To
make sure the Date column is used for X values, delete the label Date in the
top left cell. That's right, completely clear the cell: select the cell and
press Delete. This blank cell helps Excel to parse the data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"QuantumLeap" <QuantumLeap@discussions.microsoft.com> wrote in message
news:875E9931-78C9-46E5-A9BA-EB3CB044EFCD@microsoft.com...
>I have files that contain measurment information that has rows in the
> following format:
>
> Date Data_Collector Measurement_A Measurement_B Measurement_A
>
> where the measurements are taken at different locations.
>
> There are multiple rows for different dates and data collectors and the
> amount of information in the file varies by how many measurements were
> taken
> in each given stretch of time.
>
> My question is, is there a way for me to have Excel automatically plot all
> of the data in the file? I would like different charts for the different
> measurements, where the x-value is the date, the y-value is the
> measurement
> value, and there are different series for each data collector.
>
> I have been doing this by selecting the data myself, but that gets to be
> very time-consuming.
>
> Thank you.
>

Kelly,

I have 9 data series, 26 measurements, and 8 files with one sheet each. I
would like the user to be able to choose the chart time period.

I was not able to see most of the information on your example because I am
on a system that does not allow macros. Is there anywhere else I can see an
example? I have done some programming in the past, but can not remember much
and would like some help getting started.

Thank you

"Kelly O'Day" wrote:

> The simple answer to your questions is Yes if you know VBA.
>
> With a combination of dynamic range names, advanced filtering and VBA,you
> can create macros to generate new charts each time period.
>
> How are your VBA skills?
>
> I suspect you want to know how. That is a little more involved.
>
> The first item is your data organization. Data organization is the most
> important factor in automatic generation of multiple chars. you will need
> to loop through your data to generate charts by measurement and series by
> data-collector. Your example data set shows three measurements, you don't
> give any indication of the number of data_collectors. More than 3-5 series
> per chart makes it hard to interpret series data.
>
> Also, you don't indicate how many files you are dealing with and the sheets
> per file.
>
> For the chart time period, do you want to be able to plot just the last X
> days of data, all data or user specified period?
>
> I have an example workbook that makes 8 charts on 1 sheet. Each chart only
> has one series. it may give you some ideas on how to proceed.
>
> Let me know if you need any follow-up help.
>
> http://processtrends.com/pg_charts_s..._XY_Charts.htm
>
> ....Kelly
>
> koday@processtrends.com
>
>
>
>
>
>
> "QuantumLeap" <QuantumLeap@discussions.microsoft.com> wrote in message
> news:875E9931-78C9-46E5-A9BA-EB3CB044EFCD@microsoft.com...
> >I have files that contain measurment information that has rows in the
> > following format:
> >
> > Date Data_Collector Measurement_A Measurement_B Measurement_A
> >
> > where the measurements are taken at different locations.
> >
> > There are multiple rows for different dates and data collectors and the
> > amount of information in the file varies by how many measurements were
> > taken
> > in each given stretch of time.
> >
> > My question is, is there a way for me to have Excel automatically plot all
> > of the data in the file? I would like different charts for the different
> > measurements, where the x-value is the date, the y-value is the
> > measurement
> > value, and there are different series for each data collector.
> >
> > I have been doing this by selecting the data myself, but that gets to be
> > very time-consuming.
> >
> > Thank you.
> >
>
>
>

"QuantumLeap" <QuantumLeap@discussions.microsoft.com> wrote in message
news:23E5AEA0-5F02-4842-9289-718FEE4C2B30@microsoft.com...
> Kelly,
>
> I have 9 data series, 26 measurements, and 8 files with one sheet each. I
> would like the user to be able to choose the chart time period.

First you said all the data in the file, now you want the user to be able to
select the time period.

> I was not able to see most of the information on your example because I am
> on a system that does not allow macros. Is there anywhere else I can see
> an
> example? I have done some programming in the past, but can not remember
> much
> and would like some help getting started.

programming = macros. If the IT nazis have turned off macros, you aren't
going to do much programming.

Fortunately you don't need VBA for many kinds of dynamic charts. The
following article shows how to set up a worksheet with dynamic ranges and
worksheet controls that allows the user to select ranges of time data:

http://pubs.logicalexpressions.com/P...cle.asp?ID=246

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______