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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|