Formula updates when new worksheet is inserted

[mehr27]

Here goes:

I have a worksheet that represents employee yearly hours for a project. 12 columns correspond to each month and the rows correspond to each employee.

I have copied this worksheet several times, each worksheet representing a different project. Each project will have the same 12 columns but the number of rows will vary depending on which employee works on the project.

All projects are divided into two categories (each worksheet says which category). I have two summary worksheets, one for each category, that total everyone's hours per month for all projects belonging to that category.

Here is my request:

I will be adding new projects (worksheets) into the mix. As it is, I'm manually editing the category summaries to add each employee's new project hours.

I'd like to have my final two category summaries recognize that a new project has been inserted, find the category it belongs to, find the employee's name and then update the summary.

Can this be done? I'm using Excel 2003.

Thanks,
Glenn

 

[SacreBleu]

Why not use just one worksheet for all the projects? Then, you would allocate the same number of rows (every employee) to each project. If an employee isn't working on given project, then number of hours = zero.

The project label or number would then occur every, say, 20th row, and you could use the MATCH and OFFSET functions to lookup data.

 

[Mutt]

You could define a function to go thru all worksheets, check category (input variable) and name (input variable) and sumup the required hours if both true.

Simple example : if category on each project sheet is in A1 and employees names are listed in rows 3-100 in column A and their hours for the project in column B :

Function sumup(catname, empname)
For Each one In Worksheets
If Worksheets(one.Name).Range("A1").Value = catname Then
For i = 3 To 100
If Worksheets(one.Name).Cells(i, 1).Value = empname Then
sumup = sumup + Worksheets(one.Name).Cells(i, 2).Value
i = 200
End If
Next i
End If
Next one
End Function

 

[abeltio]

this seems like a job for the pivot table in excel.
if i understood your question correctly... the pivot table will do exactly that... summarize in a worksheet the hours worked by each employee for each project under category 1
and you can setup a different pivot table for the 2nd category in a different worksheet.

btw... pivot table is a colt you have to tame...

don't get frustrated when the pivot table gives you COUNT OF when you are looking for SUM OF...

when you prepare the pivot table double click on the property you want summarized and select sum of...
it will take you a couple of errors and trials.

I think it should be like that and not "trial and error" because if you do not err the first time you would not be trying again just for fun... )

to find the pivot table and pivot chart report... go to
DATA -> PIVOT TABLE AND PIVOT CHART REPORT
have fun.

HTH



saludos.
a.

 

[mehr27]

Thanks everyone. Great ideas. I am actually setting this up for another coworker who isn't as proficient at script and pivot tables so I attempted something pretty basic. These were some avenues I hadn't considered though.

I ended up placing a begin and end tab for each category and allowing the user to enter a new project in between the tabs. I also took the easy way out and just placed each coworker on every sheet. My summary sheet will add all tabs between start and end. New tabs will be added up in the summary sheet because they are placed between the start and end tabs.