Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Formula updates when new worksheet is inserted

Formula updates when new worksheet is inserted

Formula updates when new worksheet is inserted

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.


RE: Formula updates when new worksheet is inserted

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.

RE: Formula updates when new worksheet is inserted

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

RE: Formula updates when new worksheet is inserted

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
have fun.



RE: Formula updates when new worksheet is inserted

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close