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

[John Clarke]

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.

 

[Jon Peltier]

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]

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]

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/
_______