Excel 2003 Macro Help Required

[PCmad]

I have a task where I have an excel spreadsheet sent to me each week of a list of servers and the percentage of disk space used. From the spreadsheet all I have to do is find out and predict over the next few months/week the average disk space used, which I can do. The problem I have is the way in which I receive the data makes the task difficult and involes a lot of cutting and pasting unless I can find a work around. I need to somehow get all the information together first and this is the problem I am having, the actual calculations like averages I can do.

To explain, I have a column called Servers that has same server several times, for example Column A1 says Server 1 Column B Says Description which would say for example C drive Then A2 might say Server 1 again and Column B D Drive. Column C has the used percentage, Column D has the total size of the hard drive, Column E shows how much disk space has been used. As an example this is what the spreadsheet would look like :-

Week 1 (Spreadsheet 1)

A B C D E
Servers Description Used% Total Size UsedSize

Server1 C Drive 57 8789 5015
Server1 D Drive 11 7762 873
Server2 C 50 100 50
and so forth....
up to about 400 rows.

Then in Week 2 I will have another spreadsheet same columns but lets just say a server has gone or been removed. So say Server 2 has now gone not being replaced. Instead of 400 rows like week 1 I now have say 390 rows. The servers jump from say server 1 to server 3.

I then have a front page that has all the servers from week 1. What I need to do is have say for server 1 C drive the percentage used over say 52 weeks. So I would have week 1 say 50%, week 2 34 % all in the same spreadsheet.

Here is the problem, I cannot just do a straight forward copy and paste each week because each week some of the servers have gone and there might be a new server in operation as well. This means each week the amount of entries and the order of the data in cell A is never the same. Week 1 might have 400 rows, week 2 might have 390 rows. If I copy and paste the details all end up on the wrong servers.

I have tried using an autofilter, so for Column A Server 1 I can get week 1 to just show me any entries for Server 1. Then I can copy the Used Space, Total Space Used, Percentage space and paste into my main front page spreadsheet. Still on week 1 spreadsheet, I then filter out for Server 2 then copy and paste in server 2 entries. But this is very time consuming.

I so need to know if there is a macro or anyone knows of a macro that when run would ask me for the name of the server, like say a box that inputs my answer, then from my answer, looks up the values total used, percentage used, total size and returns them to my Front Page under the exact same answer that I typed in the box. All columns have autofilters.

I've already tried asking if there is anyway I could have the data in a different way like say servers as columns, instead of rows. But I still have a problem when servers have gone and new ones added etc.

Sorry for the long post, very hard to explain what I mean. Can anyone me out, or any ideas? Thanks in advance for any suggestions.

 

[SkipVought]

Hi,

ALL, ALL, ALL your data ought to be in ONE, ONE, ONE table, INCLUDING a column for DATE, like...
[tt]
Servers Description Used% Total Size UsedSize RecDate
Server1 C Drive 57 8789 5015 1/23/2006
Server1 D Drive 11 7762 873 1/23/2006
Server2 C 50 100 50 1/23/2006
[/tt]
Then it's just a matter of using some of Excel's build-in functionality to report on server usage.

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}