Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Employee Tracking
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a template for Employee annual leave tracking | Excel Discussion (Misc queries) | |||
Employee Tracking Schedule | Excel Discussion (Misc queries) | |||
Fromula to take employee # out of employee name field | Excel Discussion (Misc queries) | |||
Referencing a newly created worksheet | Excel Worksheet Functions |