A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to create graphs in a monthly report where the base data can change



 
 
Thread Tools Display Modes
  #1  
Old June 23rd, 2004, 07:51 AM
John Clarke
external usenet poster
 
Posts: n/a
Default How to create graphs in a monthly report where the base data can change

Firstly an apology if this has been asked for before, I have looked
through but am unable to find anything matching my request.

Each month I have to produce a report from our business system.
Primarily the report consists of many graphs.

I have created a report already that each chart references an
individual spreadsheet but the downloading from the business system
takes a couple of hours as each data set has to be individually
compiled and downloaded.
It is possible to download all the data in one go, but I am not sure
how I go about then linking the data to each graph.
Typically the individual downloaded data might be like this:-

Sales (Product A) data......
Stock (Product A) data......
Safety cover (Product A) data......
Production (Product A) data......

Whereas the multiple download would look like this:-

Sales (Product A) data......
Sales (Product B) data......
Sales (Product C) data......
Stock (Product A) data......
Stock (Product B) data......
Stock (Product C) data......
Safety cover (Product A) data......
Safety cover (Product B) data......
Safety cover (Product C) data......
Production (Product A) data......
Production (Product B) data......
Production (Product C) data......

There are a couple of complications in this.
1. I cannot guarantee that each month the order A, B, C, will remain,
maybe a new product would be added in the middle of the order.
2. The downloaded spreadsheet is overwritten each time it is
downloaded so I cannot write macros within it.

I think I need to do it with range names but I am not too sure on how
to write these into the graph series names. Before I start on this
project I need a steer on what direction I should take.

My level of Excel is not to bad, I can write basic macros if required.
If anyone can point me to a website where there maybe there are some
examples I could adopt or maybe send me a spreadsheet with a working
example.

I actually have had to split this out over three reports as I get an
error if I try to cram it into one spreadsheet. I think this is the
bug where there is more than 128 graphs in the spreadsheet.

We are running Excel 97.

Thanks in advance and maybe I can get a few brownie points from my
boss in solving this one.

John C.
  #2  
Old June 23rd, 2004, 05:36 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default How to create graphs in a monthly report where the base datacan change

Hi John -

First let me point out that if you know how to go about it with macros,
you can always keep the macros in another workbook, so the data update
doesn't delete them. Second, you can have charts on different worksheets
(or even workbooks), referring to data elsewhere, so the 128 charts
limit need not cause a hardship. Put sales data on a worksheet called
Sales Charts, stock data on Stock Charts, etc. It might be advantageous
to split up the data as well.

How are you processing the updated data currently to prepare to chart
it? Straight select and chart from the imported data? Or intermediate
processing first (e.g., pivot table, filter, etc.)?

Any of these operations can be done via VBA. Break up your process into
steps, and you might find it easier to automate the smaller steps, while
building the whole program. Come back to the group with specific questions.

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

John Clarke wrote:

Firstly an apology if this has been asked for before, I have looked
through but am unable to find anything matching my request.

Each month I have to produce a report from our business system.
Primarily the report consists of many graphs.

I have created a report already that each chart references an
individual spreadsheet but the downloading from the business system
takes a couple of hours as each data set has to be individually
compiled and downloaded.
It is possible to download all the data in one go, but I am not sure
how I go about then linking the data to each graph.
Typically the individual downloaded data might be like this:-

Sales (Product A) data......
Stock (Product A) data......
Safety cover (Product A) data......
Production (Product A) data......

Whereas the multiple download would look like this:-

Sales (Product A) data......
Sales (Product B) data......
Sales (Product C) data......
Stock (Product A) data......
Stock (Product B) data......
Stock (Product C) data......
Safety cover (Product A) data......
Safety cover (Product B) data......
Safety cover (Product C) data......
Production (Product A) data......
Production (Product B) data......
Production (Product C) data......

There are a couple of complications in this.
1. I cannot guarantee that each month the order A, B, C, will remain,
maybe a new product would be added in the middle of the order.
2. The downloaded spreadsheet is overwritten each time it is
downloaded so I cannot write macros within it.

I think I need to do it with range names but I am not too sure on how
to write these into the graph series names. Before I start on this
project I need a steer on what direction I should take.

My level of Excel is not to bad, I can write basic macros if required.
If anyone can point me to a website where there maybe there are some
examples I could adopt or maybe send me a spreadsheet with a working
example.

I actually have had to split this out over three reports as I get an
error if I try to cram it into one spreadsheet. I think this is the
bug where there is more than 128 graphs in the spreadsheet.

We are running Excel 97.

Thanks in advance and maybe I can get a few brownie points from my
boss in solving this one.

John C.


  #3  
Old June 24th, 2004, 11:09 PM
John Clarke
external usenet poster
 
Posts: n/a
Default How to create graphs in a monthly report where the base data can change

Thanks Jon

I will come back to the forum with more specific issues. My major
concern is how to manage the initial data. As I mention in my original
message, I already have a simple but time consuming method. It is more
about how I can use named ranges for referencing the data in the
series. I was hoping that someone has a web site showing how to. I
have looked on yours, although very useful, I was unable to find named
ranges.

John Clarke


Jon Peltier wrote in message ...
Hi John -

First let me point out that if you know how to go about it with macros,
you can always keep the macros in another workbook, so the data update
doesn't delete them. Second, you can have charts on different worksheets
(or even workbooks), referring to data elsewhere, so the 128 charts
limit need not cause a hardship. Put sales data on a worksheet called
Sales Charts, stock data on Stock Charts, etc. It might be advantageous
to split up the data as well.

How are you processing the updated data currently to prepare to chart
it? Straight select and chart from the imported data? Or intermediate
processing first (e.g., pivot table, filter, etc.)?

Any of these operations can be done via VBA. Break up your process into
steps, and you might find it easier to automate the smaller steps, while
building the whole program. Come back to the group with specific questions.

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

John Clarke wrote:

Firstly an apology if this has been asked for before, I have looked
through but am unable to find anything matching my request.

Each month I have to produce a report from our business system.
Primarily the report consists of many graphs.

I have created a report already that each chart references an
individual spreadsheet but the downloading from the business system
takes a couple of hours as each data set has to be individually
compiled and downloaded.
It is possible to download all the data in one go, but I am not sure
how I go about then linking the data to each graph.
Typically the individual downloaded data might be like this:-

Sales (Product A) data......
Stock (Product A) data......
Safety cover (Product A) data......
Production (Product A) data......

Whereas the multiple download would look like this:-

Sales (Product A) data......
Sales (Product B) data......
Sales (Product C) data......
Stock (Product A) data......
Stock (Product B) data......
Stock (Product C) data......
Safety cover (Product A) data......
Safety cover (Product B) data......
Safety cover (Product C) data......
Production (Product A) data......
Production (Product B) data......
Production (Product C) data......

There are a couple of complications in this.
1. I cannot guarantee that each month the order A, B, C, will remain,
maybe a new product would be added in the middle of the order.
2. The downloaded spreadsheet is overwritten each time it is
downloaded so I cannot write macros within it.

I think I need to do it with range names but I am not too sure on how
to write these into the graph series names. Before I start on this
project I need a steer on what direction I should take.

My level of Excel is not to bad, I can write basic macros if required.
If anyone can point me to a website where there maybe there are some
examples I could adopt or maybe send me a spreadsheet with a working
example.

I actually have had to split this out over three reports as I get an
error if I try to cram it into one spreadsheet. I think this is the
bug where there is more than 128 graphs in the spreadsheet.

We are running Excel 97.

Thanks in advance and maybe I can get a few brownie points from my
boss in solving this one.

John C.

  #4  
Old June 25th, 2004, 02:22 AM
Jon Peltier
external usenet poster
 
Posts: n/a
Default How to create graphs in a monthly report where the base datacan change

John -

Look at the dynamic chart pages, which include examples and many links:

http://peltiertech.com/Excel/Charts/Dynamics.html

Post back with questions.

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

John Clarke wrote:
Thanks Jon

I will come back to the forum with more specific issues. My major
concern is how to manage the initial data. As I mention in my original
message, I already have a simple but time consuming method. It is more
about how I can use named ranges for referencing the data in the
series. I was hoping that someone has a web site showing how to. I
have looked on yours, although very useful, I was unable to find named
ranges.

John Clarke


Jon Peltier wrote in message ...

Hi John -

First let me point out that if you know how to go about it with macros,
you can always keep the macros in another workbook, so the data update
doesn't delete them. Second, you can have charts on different worksheets
(or even workbooks), referring to data elsewhere, so the 128 charts
limit need not cause a hardship. Put sales data on a worksheet called
Sales Charts, stock data on Stock Charts, etc. It might be advantageous
to split up the data as well.

How are you processing the updated data currently to prepare to chart
it? Straight select and chart from the imported data? Or intermediate
processing first (e.g., pivot table, filter, etc.)?

Any of these operations can be done via VBA. Break up your process into
steps, and you might find it easier to automate the smaller steps, while
building the whole program. Come back to the group with specific questions.

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

John Clarke wrote:


Firstly an apology if this has been asked for before, I have looked
through but am unable to find anything matching my request.

Each month I have to produce a report from our business system.
Primarily the report consists of many graphs.

I have created a report already that each chart references an
individual spreadsheet but the downloading from the business system
takes a couple of hours as each data set has to be individually
compiled and downloaded.
It is possible to download all the data in one go, but I am not sure
how I go about then linking the data to each graph.
Typically the individual downloaded data might be like this:-

Sales (Product A) data......
Stock (Product A) data......
Safety cover (Product A) data......
Production (Product A) data......

Whereas the multiple download would look like this:-

Sales (Product A) data......
Sales (Product B) data......
Sales (Product C) data......
Stock (Product A) data......
Stock (Product B) data......
Stock (Product C) data......
Safety cover (Product A) data......
Safety cover (Product B) data......
Safety cover (Product C) data......
Production (Product A) data......
Production (Product B) data......
Production (Product C) data......

There are a couple of complications in this.
1. I cannot guarantee that each month the order A, B, C, will remain,
maybe a new product would be added in the middle of the order.
2. The downloaded spreadsheet is overwritten each time it is
downloaded so I cannot write macros within it.

I think I need to do it with range names but I am not too sure on how
to write these into the graph series names. Before I start on this
project I need a steer on what direction I should take.

My level of Excel is not to bad, I can write basic macros if required.
If anyone can point me to a website where there maybe there are some
examples I could adopt or maybe send me a spreadsheet with a working
example.

I actually have had to split this out over three reports as I get an
error if I try to cram it into one spreadsheet. I think this is the
bug where there is more than 128 graphs in the spreadsheet.

We are running Excel 97.

Thanks in advance and maybe I can get a few brownie points from my
boss in solving this one.

John C.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.