Employee Tracking

  • Thread starter Gymclass14 via OfficeKB.com
  • Start date
G

Gymclass14 via OfficeKB.com

I need to create a Employee specific worksheet. The information I need is
based off of a daily production worsheet. I can not figure out a formula to
use. I need to have a total if the employees name is entered. This would
have to be the result across 30 other worksheets. I tried this one, with no
luck: =SUM((Day1:Day31!C5="Alex")*(Day1:Day31!C6)). Hopefully this givs the
idea of what I am trying to do. Please help!! This is driving me crazy!!!!!!
!!!!
 
R

RagDyeR

XL is not too strong with 3D functions.

You'll have to start off by creating a list of your sheet names.
Needless to say, this list must match *exactly* the names on the sheet tabs.
Say A1 to A31.

Then try this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A31&"'!C5"),"Alex",INDIRECT("'"&A1:A31&"'!
C6")))

You could assign a name to the sheet range (A1 to A31), say "Days",
And also assign a cell to contain the name of the employee, say B1.

Then, it would look like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&days&"'!C5"),B1,INDIRECT("'"&days&"'!C6")))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I need to create a Employee specific worksheet. The information I need is
based off of a daily production worsheet. I can not figure out a formula to
use. I need to have a total if the employees name is entered. This would
have to be the result across 30 other worksheets. I tried this one, with no
luck: =SUM((Day1:Day31!C5="Alex")*(Day1:Day31!C6)). Hopefully this givs the
idea of what I am trying to do. Please help!! This is driving me
crazy!!!!!!
!!!!
 
G

Gymclass14 via OfficeKB.com

RagDyeR said:
XL is not too strong with 3D functions.

You'll have to start off by creating a list of your sheet names.
Needless to say, this list must match *exactly* the names on the sheet tabs.
Say A1 to A31.

Then try this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A31&"'!C5"),"Alex",INDIRECT("'"&A1:A31&"'!
C6")))

You could assign a name to the sheet range (A1 to A31), say "Days",
And also assign a cell to contain the name of the employee, say B1.

Then, it would look like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&days&"'!C5"),B1,INDIRECT("'"&days&"'!C6")))

I need to create a Employee specific worksheet. The information I need is
based off of a daily production worsheet. I can not figure out a formula to
use. I need to have a total if the employees name is entered. This would
have to be the result across 30 other worksheets. I tried this one, with no
luck: =SUM((Day1:Day31!C5="Alex")*(Day1:Day31!C6)). Hopefully this givs the
idea of what I am trying to do. Please help!! This is driving me
crazy!!!!!!
!!!!
Click to expand...


I tried these formulas and still received an error. Any other ideas? Here
is exactly what I am trying to do. If a name is entered into cell C5 then it
will produce a total on another worksheet. The total worksheet would have to
have a total in C6 if the name Alex appears in C5. I hope I am explaining
this clearly. Thanks
 
R

Ragdyer

The formula that I suggested looks for the name Alex in cell C5 of *every*
WS in the WB, and then totals the contents of cell C6 in *every* WS that has
the name of Alex.

If that's not what you want, re-phrase your explanation and try to elaborate
on your set-up.
 
E

excelent

Well Ragdyer's hint works fine here
i was trying make excel formula too but no luck
so insted i tok the easey way and made a Function:

Function Tracking(Name As String, First, Last, myCell)
Dim t, v
Application.Volatile
For t = First To Last
If Sheets(t).Range("C5") = Name Then v = v + Sheets(t).Range("C6")
Next
Tracking = v
End Function

in a cell put this:

=tracking("Alex";2;6;C6)

where tracking is the Function name
"Alex" is the name to test
2 is the first sheet to look and sum of
6 is the last shet to look and sum from (in ur case i ges 31)
C6 is the cell where the values are to sum

put in a regular module.

regards p.m.
 
G

Gymclass14 via OfficeKB.com

Let me try to re-explain what i am trying to do. I am trying to track an
employees performance across a month's time frame. The worksheets I
currently have are a range from Day1 to Day31. I want to have a total of
these Worksheets on one called EmployeeA. The information will be entered
daily on one of the worksheets named Day1 to Day 31. The name of the
employee is in C5. The total number of hours worked for that day is in C6.
I need to have a total show up on the EmployyeA worksheet when the name Alex
is entered into C5 on Day1 to Day31. The total should be in C6 on the
EmployeeA worksheet, and should be a total of all the C6 cells across all the
worksheets. This way if the name Alex is in C5, then his hours are totaled
in C6 and I can look at the worksheet EmployeeA at the end of the month and
see his work. I tried this formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Day1:Day31&"'!C5"),"Alex",INDIRECT("'"&Day1:
Day31&"'!C6")))


But I had no luck. Hopefully this explains it better. Thanks to everyone
for their help so far.
 
R

Ragdyer

My suggested formula will work *exactly* as you described what you wanted to
accomplish.

*BUT* ... you did *not* follow my instructions ! ! !

You did *not* make a list of your sheet names ... did you?

Re-read my post and follow the instructions *exactly*.

You could change the *location* of your list of sheet names from A1:A31 to
whatever range you wish, and then reference *that location* in the formula.

That's the only change you should make!
 
G

Gymclass14 via OfficeKB.com

How do I make a list of my sheet names so the formula works? Thanks for all
the help. I am new to using Excel and appreciate all the help!!!
 
R

Ragdyer

If I correctly read the formula that *you* constructed, it should be very
easy.

I see that your sheet names, as you entered in your formula, are:
Day1
Day2
Day3
.... etc.
With *no* space between the Day and the number ... Is that correct?

So, let's say that you wish to place this sheet list in an out-of-the-way
location of your sheet, say Z1 to Z31.

In Z1 enter:
Day1

Then, click back into Z1.
Hover the cursor over the lower right corner of the selected cell Z1, until
the cursor changes from a fat white cross to a skinny black cross.
Then click and drag down to Z31.

The Day1 should automatically increment as you drag down the column to
Day31.

That's all it takes!
It's fortunate that you're using this type of sheet name, which can easily
be autofilled down a column.
You would otherwise have to key in each name if the names were complicated.

Now, this formula should work for you:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z31&"'!C5"),"Alex",INDIRECT("'"&Z1:Z31&"'!
C6")))
 
Top