Excel Graph Problem and Data Manipulation

[PCmad]

I'm using Excel 2003 and have a file that is generated from another program that shows each week how much disk space has been used on each hard drive for each server. We have about 200 servers some have up to 5 drives, (C - G). I have been asked to come up with a graph for each server to show the amount of space each week that is used and to use tools in excel to analyse the average, mean and to predict future storage needs. This bit I can do using excel functions. This one file will change each week by having one additional column added showing the amount of space used for the previous week and will continue to grow.

The Problem I have is how to get the information together to draw up separate graphs and how I can then get the graphs to automatically add in the extra column each week without any manual intervention as I have 200 servers.

To explain further this is how the spreadsheet looks.
Column A (server) Column B (Total Size) Column C (% Used)
Server 1 C Drive 9216 80%
Server 1 D Drive 9216 92%
Server 1 E Drive 9216 80%
Server 1 F Drive 9216 72%
Server 1 G Drive 9216 60%
Server 2 C Drive 10000 70%
Server 2 D Drive 20000 65%
and so on down to server 200

Column D has the next weeks percentage eg 80% for week 2, Column E 85% for Week 3 and so on. Each week an additional column is added with the last weeks percentage figures.

What I need to do is somehow from the main sheet create a worktab by server for each server. On this worksheet I would then have 5 entries for each drive the server has along with the percentage used each week. Not only do I need to find a way to do this without doing manually but I need to find a way to be able to get the information updated when I get the new weekly percentage used figure as well. Once I have done this I would then end up with about 200 tabs in the worksheet one tab for each server at the bottom.

I would then need to create the graph for each worksheet and again would like to just do one and have the other 199 somehow done automatically so I don't have to manually create all of these. Once the graph has been created I then need to find a way to get it to automatically update with the new figures of the percentage used each week.

I am sorry if this all sounds long winded I have been trying to come up with a solution for many months but each time I am falling flat on my face with another hurdle and really do not know what is the best way to complete this.

Should I be using something else rather than Excel ? Is there perhaps another way I could pursue in excel other than the way I am trying ? Any help and suggestions would be appreciated as I really need to come up with some sort of plan as this is for work purposes and I am already over the time allowed really and sinking fast!. I can if it helps send an example of the spreadsheet. Thank you for any help and advice.

 

[SkipVought]

Hi,

"What I need to do is somehow from the main sheet create a worktab by server for each server. "

This is unnecessary. You can Use the Chart Wizard on your source data. I would parse the server & Drive into separate columns, but that is not absolutely necessary.

If you turn on the AutoFilter -- Data/Filter/AutoFilter -- you can filter on each server using a CUSTOM filter in the dropdown, with a CRITERIA of Begins with or Contains.

I can be slicked up with a little VBA code to have another ComboBox with the server names that you can select and then the macro would change the AutoFilter criteria value.

I do this alot. ONE data source. ONE chart. DropDown box to control the subset to display. With multiple sheets and charts is multiplies the maintenance of the system.

Let me know how you want to proceed.

Skip,

_[red][/red]