Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Employee Tracking

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!!!!!!
!!!!

--
Message posted via http://www.officekb.com

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Employee Tracking

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"),"Ale x",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,IND IRECT("'"&days&"'!C6")))

--

HTH,

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


"Gymclass14 via OfficeKB.com" <u26166@uwe wrote in message
news:66ba6f2ae625b@uwe...
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!!!!!!
!!!!

--
Message posted via http://www.officekb.com


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Employee Tracking

RagDyeR wrote:
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"),"Al ex",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,IN DIRECT("'"&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!!!!!!
!!!!



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

--
Message posted via http://www.officekb.com

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Employee Tracking

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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Gymclass14 via OfficeKB.com" <u26166@uwe wrote in message
news:66bd76141ebba@uwe...
RagDyeR wrote:
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"),"Al ex",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,IN DIRECT("'"&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!!!!!!
!!!!



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

--
Message posted via http://www.officekb.com


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 695
Default Employee Tracking

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.


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Employee Tracking

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.

--
Message posted via http://www.officekb.com

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 695
Default Employee Tracking

http://pmexcelent.dk/Tracking.xls

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Employee Tracking

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!
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gymclass14 via OfficeKB.com" <u26166@uwe wrote in message
news:66c61a5edec07@uwe...
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.

--
Message posted via http://www.officekb.com


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Employee Tracking

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!!!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Employee Tracking

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, a
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"),"Ale x",INDIRECT("'"&Z1:Z31&"'!
C6")))


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gymclass14 via OfficeKB.com" <u26166@uwe wrote in message
news:66c91a3c370c6@uwe...
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!!!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default Employee Tracking

It worked!! Thanks so much for your continued help.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Employee Tracking

Glad you finally got it all straightened out.
Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gymclass14 via OfficeKB.com" <u26166@uwe wrote in message
news:66ca65a295546@uwe...
It worked!! Thanks so much for your continued help.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200609/1


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need a template for Employee annual leave tracking Pamela Aranguiz Excel Discussion (Misc queries) 1 January 12th 06 02:35 AM
Employee Tracking Schedule littlebit Excel Discussion (Misc queries) 1 October 4th 05 01:00 AM
Fromula to take employee # out of employee name field mikeburg Excel Discussion (Misc queries) 9 September 12th 05 03:41 AM
Referencing a newly created worksheet Charyn Excel Worksheet Functions 2 May 2nd 05 04:13 AM


All times are GMT +1. The time now is 10:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"