What functions to use for Timesheet to auto-populate according to work schedule ID

J

jsobczak1

New Member
Joined
Feb 28, 2014
Messages
6
I'm working on a monthly timesheet template to autofill 8 hours on the days the employee works and 0 for their days off. On the bottom of the sheet it will have the total forcasted hours. I keep getting error messages when trying to write the formula/function. The layout is as follows:

TIMESHEET TAB
AJ3=work schedule code (AA1)
M3=employee name from drop list
V3=employee ID from VLookup formula
AI3=Period ending
B7-AF7=date (1,2,3,etc)
B8-AF8=day of the week (Sat,Sun,Mon,etc)...auto changes when month changes

LOOKUP TAB
Employee info table-
A1:C:50=Employee Name, ID, Assigned Schedule Number
Work Schedule table-
Columns as days of week (Sun-Sat) col P:V
Rows as schedule number (AA1-AA7) row 2:8
rows and columns filled with 8 and 0 for Reg Days work and Days off

I want to fill hours in row B9:AF9 on the timesheet with either 8 or 0 depending on the work schedule (table ref) and day of the week (row $B$8:$AF$8)).

I've tried Lookup function but I get a #Name error probably because the schedule number is VLookup value?? Hope this makes sense. Tried INDEX, MATCH, LOOKUP... I'm not sure where to start. Thanks in advance for help!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Maybe you can post an image of your two sheets (simplified, hide all non relevant columns). I tried to construct your Lookup sheet without much luck.
 
Upvote 0
<a href='http://<a href="http://s220.photobucket.com/user/jsobczak1/media/SHEET1TIMESHEET_zps358f4e5c.png.html" target="_blank"><img src="http://i220.photobucket.com/albums/dd175/jsobczak1/SHEET1TIMESHEET_zps358f4e5c.png" border="0" alt="Sechedule photo SHEET1TIMESHEET_zps358f4e5c.png"/></a>' target="_blank">http://

<a href='http://<a href="http://s220.photobucket.com/user/jsobczak1/media/61540846-9fc3-4988-a53a-8581a23a9006_zps49f7939e.png.html" target="_blank"><img src="http://i220.photobucket.com/albums/dd175/jsobczak1/61540846-9fc3-4988-a53a-8581a23a9006_zps49f7939e.png" border="0" alt="Schedule Lookup data photo 61540846-9fc3-4988-a53a-8581a23a9006_zps49f7939e.png"/></a>' target="_blank">http://


Excellent! Thanks for the suggestion. Here's schedule and lookups links. I couldn't get the image to attach. Let me know if you have any questions.
 
Last edited:
Upvote 0

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
3Orange CloeID12345AA1
4Period
5Apr-14
6
7Date123456789101112131415161718192021222324252627282930 
8DayNr345671234567123456712345671234 
9DayTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWed 
10888800888880088888008888800888 
Timesheet
Cell Formulas
RangeFormula
AD7=IF(MONTH(DATE(YEAR($AG$5),MONTH($AG$5),AC7+1))=MONTH($AG$5),AC7+1,"")
AD8=IF(ISNUMBER(AD7),WEEKDAY(DATEVALUE(AD7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5))),"")
AD9=IF(ISNUMBER(AD8),INDEX(WeekDays,AD8),"")
AD10=IF(ISNUMBER(AD8),VLOOKUP($T$3,Schedule,AD8+1),"")
AE7=IF(AD7,IF(MONTH(DATE(YEAR($AG$5),MONTH($AG$5),AD7+1))=MONTH($AG$5),AD7+1,""),"")
AE8=IF(ISNUMBER(AE7),WEEKDAY(DATEVALUE(AE7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5))),"")
AE9=IF(ISNUMBER(AE8),INDEX(WeekDays,AE8),"")
AE10=IF(ISNUMBER(AE8),VLOOKUP($T$3,Schedule,AE8+1),"")
AF7=IF(AE7,IF(MONTH(DATE(YEAR($AG$5),MONTH($AG$5),AE7+1))=MONTH($AG$5),AE7+1,""),"")
AF8=IF(ISNUMBER(AF7),WEEKDAY(DATEVALUE(AF7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5))),"")
AF9=IF(ISNUMBER(AF8),INDEX(WeekDays,AF8),"")
AF10=IF(ISNUMBER(AF8),VLOOKUP($T$3,Schedule,AF8+1),"")
B8=WEEKDAY(DATEVALUE(B7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
B9=INDEX(WeekDays,B8)
B10=VLOOKUP($T$3,Schedule,B8+1)
C8=WEEKDAY(DATEVALUE(C7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
C9=INDEX(WeekDays,C8)
C10=VLOOKUP($T$3,Schedule,C8+1)
D8=WEEKDAY(DATEVALUE(D7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
D9=INDEX(WeekDays,D8)
D10=VLOOKUP($T$3,Schedule,D8+1)
E8=WEEKDAY(DATEVALUE(E7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
E9=INDEX(WeekDays,E8)
E10=VLOOKUP($T$3,Schedule,E8+1)
F8=WEEKDAY(DATEVALUE(F7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
F9=INDEX(WeekDays,F8)
F10=VLOOKUP($T$3,Schedule,F8+1)
G8=WEEKDAY(DATEVALUE(G7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
G9=INDEX(WeekDays,G8)
G10=VLOOKUP($T$3,Schedule,G8+1)
H8=WEEKDAY(DATEVALUE(H7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
H9=INDEX(WeekDays,H8)
H10=VLOOKUP($T$3,Schedule,H8+1)
I8=WEEKDAY(DATEVALUE(I7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
I9=INDEX(WeekDays,I8)
I10=VLOOKUP($T$3,Schedule,I8+1)
J8=WEEKDAY(DATEVALUE(J7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
J9=INDEX(WeekDays,J8)
J10=VLOOKUP($T$3,Schedule,J8+1)
K8=WEEKDAY(DATEVALUE(K7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
K9=INDEX(WeekDays,K8)
K10=VLOOKUP($T$3,Schedule,K8+1)
L8=WEEKDAY(DATEVALUE(L7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
L9=INDEX(WeekDays,L8)
L10=VLOOKUP($T$3,Schedule,L8+1)
M8=WEEKDAY(DATEVALUE(M7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
M9=INDEX(WeekDays,M8)
M10=VLOOKUP($T$3,Schedule,M8+1)
N8=WEEKDAY(DATEVALUE(N7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
N9=INDEX(WeekDays,N8)
N10=VLOOKUP($T$3,Schedule,N8+1)
O8=WEEKDAY(DATEVALUE(O7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
O9=INDEX(WeekDays,O8)
O10=VLOOKUP($T$3,Schedule,O8+1)
P8=WEEKDAY(DATEVALUE(P7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
P9=INDEX(WeekDays,P8)
P10=VLOOKUP($T$3,Schedule,P8+1)
Q8=WEEKDAY(DATEVALUE(Q7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
Q9=INDEX(WeekDays,Q8)
Q10=VLOOKUP($T$3,Schedule,Q8+1)
R8=WEEKDAY(DATEVALUE(R7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
R9=INDEX(WeekDays,R8)
R10=VLOOKUP($T$3,Schedule,R8+1)
S8=WEEKDAY(DATEVALUE(S7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
S9=INDEX(WeekDays,S8)
S10=VLOOKUP($T$3,Schedule,S8+1)
T8=WEEKDAY(DATEVALUE(T7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
T9=INDEX(WeekDays,T8)
T10=VLOOKUP($T$3,Schedule,T8+1)
U8=WEEKDAY(DATEVALUE(U7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
U9=INDEX(WeekDays,U8)
U10=VLOOKUP($T$3,Schedule,U8+1)
V8=WEEKDAY(DATEVALUE(V7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
V9=INDEX(WeekDays,V8)
V10=VLOOKUP($T$3,Schedule,V8+1)
W8=WEEKDAY(DATEVALUE(W7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
W9=INDEX(WeekDays,W8)
W10=VLOOKUP($T$3,Schedule,W8+1)
X8=WEEKDAY(DATEVALUE(X7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
X9=INDEX(WeekDays,X8)
X10=VLOOKUP($T$3,Schedule,X8+1)
Y8=WEEKDAY(DATEVALUE(Y7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
Y9=INDEX(WeekDays,Y8)
Y10=VLOOKUP($T$3,Schedule,Y8+1)
Z8=WEEKDAY(DATEVALUE(Z7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
Z9=INDEX(WeekDays,Z8)
Z10=VLOOKUP($T$3,Schedule,Z8+1)
AA8=WEEKDAY(DATEVALUE(AA7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
AA9=INDEX(WeekDays,AA8)
AA10=VLOOKUP($T$3,Schedule,AA8+1)
AB8=WEEKDAY(DATEVALUE(AB7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
AB9=INDEX(WeekDays,AB8)
AB10=VLOOKUP($T$3,Schedule,AB8+1)
AC8=WEEKDAY(DATEVALUE(AC7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
AC9=INDEX(WeekDays,AC8)
AC10=VLOOKUP($T$3,Schedule,AC8+1)
Named Ranges
NameRefers ToCells
Schedule=Lookup!$O$3:$V$8
WeekDays=Lookup!$Y$3:$Y$9


In Lookup I have named the table with the schedule codes and the hours as 'Schedule' for in the lookup formula


Excel 2010
OPQRSTUV
3SunMonTueWedThuFriSat
4AA10888880
5AB10088888
6AC18008888
7AD18800888
8AE18880088
Lookup
 
Upvote 0
BIG Thanks! I see the "writing", let me try it out and I'll keep you posted. I already have some auto pops for the month & year and the day to fill according to the date. I think I can merge both concepts... let me work on it this weekend. Appreciate your help!!! So, so very much :)
J ;)

sijpie said:
Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
3Orange CloeID12345AA1
4Period
5Apr-14
6
7Date123456789101112131415161718192021222324252627282930
8DayNr345671234567123456712345671234
9DayTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWed
10888800888880088888008888800888

<tbody>
</tbody>
Timesheet​

Worksheet Formulas
CellFormula
AD7=IF(MONTH(DATE(YEAR($AG$5),MONTH($AG$5),AC7+1))=MONTH($AG$5),AC7+1,"")
AE7=IF(AD7,IF(MONTH(DATE(YEAR($AG$5),MONTH($AG$5),AD7+1))=MONTH($AG$5),AD7+1,""),"")
AF7=IF(AE7,IF(MONTH(DATE(YEAR($AG$5),MONTH($AG$5),AE7+1))=MONTH($AG$5),AE7+1,""),"")
B8=WEEKDAY(DATEVALUE(B7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
C8=WEEKDAY(DATEVALUE(C7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
D8=WEEKDAY(DATEVALUE(D7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
E8=WEEKDAY(DATEVALUE(E7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
F8=WEEKDAY(DATEVALUE(F7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
G8=WEEKDAY(DATEVALUE(G7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
H8=WEEKDAY(DATEVALUE(H7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
I8=WEEKDAY(DATEVALUE(I7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
J8=WEEKDAY(DATEVALUE(J7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
K8=WEEKDAY(DATEVALUE(K7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
L8=WEEKDAY(DATEVALUE(L7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
M8=WEEKDAY(DATEVALUE(M7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
N8=WEEKDAY(DATEVALUE(N7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
O8=WEEKDAY(DATEVALUE(O7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
P8=WEEKDAY(DATEVALUE(P7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
Q8=WEEKDAY(DATEVALUE(Q7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
R8=WEEKDAY(DATEVALUE(R7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
S8=WEEKDAY(DATEVALUE(S7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
T8=WEEKDAY(DATEVALUE(T7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
U8=WEEKDAY(DATEVALUE(U7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
V8=WEEKDAY(DATEVALUE(V7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
W8=WEEKDAY(DATEVALUE(W7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
X8=WEEKDAY(DATEVALUE(X7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
Y8=WEEKDAY(DATEVALUE(Y7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
Z8=WEEKDAY(DATEVALUE(Z7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
AA8=WEEKDAY(DATEVALUE(AA7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
AB8=WEEKDAY(DATEVALUE(AB7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
AC8=WEEKDAY(DATEVALUE(AC7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5)))
AD8=IF(ISNUMBER(AD7),WEEKDAY(DATEVALUE(AD7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5))),"")
AE8=IF(ISNUMBER(AE7),WEEKDAY(DATEVALUE(AE7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5))),"")
AF8=IF(ISNUMBER(AF7),WEEKDAY(DATEVALUE(AF7&"-"&MONTH($AG$5)&"-"&YEAR($AG$5))),"")
B9=INDEX(WeekDays,B8)
C9=INDEX(WeekDays,C8)
D9=INDEX(WeekDays,D8)
E9=INDEX(WeekDays,E8)
F9=INDEX(WeekDays,F8)
G9=INDEX(WeekDays,G8)
H9=INDEX(WeekDays,H8)
I9=INDEX(WeekDays,I8)
J9=INDEX(WeekDays,J8)
K9=INDEX(WeekDays,K8)
L9=INDEX(WeekDays,L8)
M9=INDEX(WeekDays,M8)
N9=INDEX(WeekDays,N8)
O9=INDEX(WeekDays,O8)
P9=INDEX(WeekDays,P8)
Q9=INDEX(WeekDays,Q8)
R9=INDEX(WeekDays,R8)
S9=INDEX(WeekDays,S8)
T9=INDEX(WeekDays,T8)
U9=INDEX(WeekDays,U8)
V9=INDEX(WeekDays,V8)
W9=INDEX(WeekDays,W8)
X9=INDEX(WeekDays,X8)
Y9=INDEX(WeekDays,Y8)
Z9=INDEX(WeekDays,Z8)
AA9=INDEX(WeekDays,AA8)
AB9=INDEX(WeekDays,AB8)
AC9=INDEX(WeekDays,AC8)
AD9=IF(ISNUMBER(AD8),INDEX(WeekDays,AD8),"")
AE9=IF(ISNUMBER(AE8),INDEX(WeekDays,AE8),"")
AF9=IF(ISNUMBER(AF8),INDEX(WeekDays,AF8),"")
B10=VLOOKUP($T$3,Schedule,B8+1)
C10=VLOOKUP($T$3,Schedule,C8+1)
D10=VLOOKUP($T$3,Schedule,D8+1)
E10=VLOOKUP($T$3,Schedule,E8+1)
F10=VLOOKUP($T$3,Schedule,F8+1)
G10=VLOOKUP($T$3,Schedule,G8+1)
H10=VLOOKUP($T$3,Schedule,H8+1)
I10=VLOOKUP($T$3,Schedule,I8+1)
J10=VLOOKUP($T$3,Schedule,J8+1)
K10=VLOOKUP($T$3,Schedule,K8+1)
L10=VLOOKUP($T$3,Schedule,L8+1)
M10=VLOOKUP($T$3,Schedule,M8+1)
N10=VLOOKUP($T$3,Schedule,N8+1)
O10=VLOOKUP($T$3,Schedule,O8+1)
P10=VLOOKUP($T$3,Schedule,P8+1)
Q10=VLOOKUP($T$3,Schedule,Q8+1)
R10=VLOOKUP($T$3,Schedule,R8+1)
S10=VLOOKUP($T$3,Schedule,S8+1)
T10=VLOOKUP($T$3,Schedule,T8+1)
U10=VLOOKUP($T$3,Schedule,U8+1)
V10=VLOOKUP($T$3,Schedule,V8+1)
W10=VLOOKUP($T$3,Schedule,W8+1)
X10=VLOOKUP($T$3,Schedule,X8+1)
Y10=VLOOKUP($T$3,Schedule,Y8+1)
Z10=VLOOKUP($T$3,Schedule,Z8+1)
AA10=VLOOKUP($T$3,Schedule,AA8+1)
AB10=VLOOKUP($T$3,Schedule,AB8+1)
AC10=VLOOKUP($T$3,Schedule,AC8+1)
AD10=IF(ISNUMBER(AD8),VLOOKUP($T$3,Schedule,AD8+1),"")
AE10=IF(ISNUMBER(AE8),VLOOKUP($T$3,Schedule,AE8+1),"")
AF10=IF(ISNUMBER(AF8),VLOOKUP($T$3,Schedule,AF8+1),"")

<tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
Schedule=Lookup!$O$3:$V$8
WeekDays=Lookup!$Y$3:$Y$9

<tbody>
</tbody>

<tbody>
</tbody>



In Lookup I have named the table with the schedule codes and the hours as 'Schedule' for in the lookup formula

Excel 2010
OPQRSTUV
3SunMonTueWedThuFriSat
4AA10888880
5AB10088888
6AC18008888
7AD18800888
8AE18880088

<tbody>
</tbody>
Lookup​
Click to expand...
 
Upvote 0
 
Upvote 0
I think it should be for cell B9:
=VLOOKUP($V$3,Schedule,WEEKDAY(DATEVALUE(B7&"-"&MONTH($AA$5)&"-"&YEAR($AA$5)))+1)

where V3 holds the schedule code for the person

So the formula establishes the day number for the date (compiled of the day in B7 and the month and year in AA5) adds 1 to this number for the correct column and then looks this up in range Schedule. So if the day is a sunday, it will produce daynumber 1 and then look up in the second column (1+1) so underneath Sunday for the correct number of hours
 
Upvote 0
Thanks so much for ALL your help. With a few changes I was able to get the hours to populate according to the employee schedule and day of the week. I ended up with =INDEX(SCHEDULE,MATCH($AJ$3,SCHEDULENR,0),MATCH(TEXT(AB$7,"ddd"),Weekdays,0))

The format of the day of the week was number (serial) and the schedule day of the week was text. So, this Works like a charm!!!!

Thanks again! I couldn't have done this without your help! :wink:
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Using Excel to create a schedule table from given data table.
Replies
1
Views
612
Herakles
H
R
  • Question
Employee Schedule Generator
Replies
7
Views
613
rdbauer83
R
L
  • Question
INDEX / MATCH Used Horizontally & Looking for a Value Based on a Dropdown List
Replies
1
Views
471
LucieLiskova
L
markkeith
  • Solved
Function or Formula to get a value for each cell from multiple logical test.
Replies
3
Views
547
markkeith
markkeith
C
  • Question
Calculating Each Hour Worked by Multiple Employees to get the total Cost of Each Hour
Replies
1
Views
561
cs25001
C

Forum statistics

Threads
1,214,918
Messages
6,122,255
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top