I was wondering if anyone can help me create a somehwhat complicated timesheet. Basically, i have a couple hundred job numbers that are being used. I want the employee to be able to punch in a job number for every 15 minutes they worked on it in a cell. At the end of the sheet, I want it to recognize all of the unique job numbers and how many times they were on the sheet and give me a list of the job numbers and a total. Can this be done?? Thanks!
If you have a column of job numbers (neither unique nor ordered) in column A and times in column B, then
Create a helper column in D with D1
=(COUNTIF($A$1:A1,A1)=1)*ROW()
and fill down the column
Then, in F1 and filled down
=IF(LARGE(D:D,ROW()),INDEX(A:A,LARGE(D:D,ROW()),1),"")
will list the job numbers from column A.
In G1 (filled down) will give the total of the times (from col. B) for each of the job numbers in col. F.
=SUMIF(A:A,F1,B:B)
If you have a header row(s), only the two occurances of LARGE(D:D,ROW()-headerRowCount) in col. F's formula need be adjusted.
Getting your employees to "punch the time clock" 32 times a day with non-fiction is a problem I don't have a solution to.
Last edited by mikerickson; 11-30-2007 at 04:18 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks