Multiple Timesheets, auto fill from data sheet

**edwinkbell** · 07-28-2009, 09:53 AM

I have been tasked with creating a workbook that will take a time log file (excel) from an electronic time clock and import the dates/times into individual employee timesheets. The timesheet layout has already been provided to me and cannot change.

I’m really struggling here and would appreciate any help.

Please reference the attached workbook for the issues described below:

1) Sheet "1_attlog" is the log that is imported from the electronic time clock.

2) Sheet "Sorting & Filtering" is what I have developed so far that manipulates the data from sheet "1_attlog" into something that can be easily understood, it also matches employee #'s to a name, and allows for date ranges to be set that copies over to the individual timesheets. Please do not change any formulas on this page if possible.

3) Sheets "#2 Dan through #10 Blank" are all individual employee timesheets that I need to import the data from "Sorting & Filtering" and populate the "Time In" (column D) and "Time Out" (column G) based on matching the data from "Sorting & Filtering" sheet, "Time Clcok ID" (column D) "Date" (column E), "Time In" (column F), "Time Out" (column G), "Emp Name" (column I).
The dates on the individual timesheets have been linked to the "Date Range" (cels F2 & F3) on the "Sorting & Filtering" sheet, please keep this link intact.

4) The individual timesheets already have formulas inplace to calculate the hours worked after the "Time In" and "Time Out" data has been imported.

Please let me know if you can help develop this.

Thanks…

**Palmetto** · 08-13-2009, 10:29 AM

On the Sorting &Filtering Worksheet
Changed the cell reference in the Date column(E) to use this formula to return a true date value

=TEXT(B7,"d-mmm-yy")+0

and set the format to that used in F2

Added a helper column (J) to create a unique lookup value by joining several cells

=IF(F7="In",D7&E7&F7,D7&E7&G7)

Created a Dynamic Named Range for the "database".
=OFFSET('Sorting & Filtering'!$D$6,0,0,COUNTA('Sorting & Filtering'!$D:$D),7)

Created a Dynamic Named Range for the unique look up values
=OFFSET('Sorting & Filtering'!$J$6,0,0,COUNTA('Sorting & Filtering'!$J:$J),1)

On Sheet #2 Dan
Removed Data Validation so that formula could be used
Created two helper columns to perform the look up of the "In" and "Out" times

Look up formula 1:
=INDEX(Database,MATCH($K$11&C14&$D$13,Val_Lookup,0),5)

Look up formula 2: =INDEX(Database,MATCH($K$11&$C14&$E$13,Val_Lookup,0),5)

Helper cells are used for efficiency to perform the look up once rather than nesting the formula in an IF function and forcing Excel to do it twice.

The look up value in the formulas join several cells to create the same type unique value as was done for the Filtering & Sorting sheet. Required to find a match

This formula is used in the "time" cells and reference the results of the look up cells
In =IF(ISNA(O14),0,O14)
Out =IF(ISNA(P14),0,P14)

Your structure on the indivdual time sheets allows for multiple clock in/out. I haven't dealt with this, but I suppose a countif could be created to see if the employee had already clocked in/out once for a given date. This would require a change in how the unique look up values are created.

HTH,

Palmetto