Automatic creation of an overview using several workbooks
 

Hello there,

I could need some help with MS Excel
I have several worksheets and I want to create an overview out of
them.

These worksheets are the activity reports for employees, they contain
the project name, the month, the employee name and the hours (s)he has
worked on this project.
Every employee has one worksheet for every project.

There a 5 different activity codes (planning, analysis, development,
pilot, deployment), all the activitues the employees are doing belong
to one of these.

Now I want to create a monthly overview of the hours for every
activity code and every project

This overview should have 6 columns:

1. The Names of the employees
2. The Project Codes
3. The Activity Codes (all these three columns are looked up in the
activity reports)

4. The amount of hours the employee worked on this project in this
activity
code, (I use the formula SUMIF)

5. The rate/hour, this is different for every employee but independent
from the project or the activity code, the rates are looked up in
another worksheet

6. The total costs, (hours x rate/hour)

Also the hours and the costs should be summarized for every activity
code and every project.

So far I have created the overview manually and have filled it with
some formulas:

To lookup the hours I use the formula SUMIF, whenever one particular
activity code is found in the activity reports the hours for this are
summarized, if there are no hours a "0" is put in

To lookup the rates/hour I use the formula VLookup, this searches for
the name of the employee and returns the assigned rate/hour

And I already put in all the sum-formulas to calculate the total
hours.

So far so good, but not good enough.

Now I want the overview to be created automatically, just by clicking
a button in one of the activity reports.

Then it should be determined how many activity reports there are and
then the overview should be filled.

I know this all sounds a bit confusing but if anybody wants to help me
I will be glad to send everything I have created so far with some more
descriptions, once you see it it becomes clear.

Thanks a lot for any help

Christian