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

[jsobczak1]

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!

 

[sijpie]

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.

 

[jsobczak1]

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

 

[jsobczak1]

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.

Thanks, sijpie!! Take a look at the post with images. :D

 

[sijpie]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
3													Orange Cloe				ID12345			AA1
4																																	Period
5																																	Apr-14
6
7	Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
8	DayNr	3	4	5	6	7	1	2	3	4	5	6	7	1	2	3	4	5	6	7	1	2	3	4	5	6	7	1	2	3	4
9	Day	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed
10		8	8	8	8	0	0	8	8	8	8	8	0	0	8	8	8	8	8	0	0	8	8	8	8	8	0	0	8	8	8
Timesheet

Cell Formulas
Range	Formula
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
Name	Refers To	Cells
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
	O	P	Q	R	S	T	U	V
3		Sun	Mon	Tue	Wed	Thu	Fri	Sat
4	AA1	0	8	8	8	8	8	0
5	AB1	0	0	8	8	8	8	8
6	AC1	8	0	0	8	8	8	8
7	AD1	8	8	0	0	8	8	8
8	AE1	8	8	8	0	0	8	8
Lookup

 

[jsobczak1]

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

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
Orange Cloe	ID12345	AA1
Period
Date
DayNr
Day

<tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Apr-14[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]Tue[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thu[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Sat[/TD]
[TD="align: right"]Sun[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thu[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Sat[/TD]
[TD="align: right"]Sun[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thu[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Sat[/TD]
[TD="align: right"]Sun[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thu[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Sat[/TD]
[TD="align: right"]Sun[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]Wed[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]

</tbody>

Timesheet​

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

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]Schedule[/TH]
[TD="align: left"]=Lookup!$O$3:$V$8[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]WeekDays[/TH]
[TD="align: left"]=Lookup!$Y$3:$Y$9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

Excel 2010

	O	P	Q	R	S	T	U	V
AA1
AB1
AC1
AD1
AE1

<tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Sun[/TD]
[TD="align: right"]Mon[/TD]
[TD="align: right"]Tue[/TD]
[TD="align: right"]Wed[/TD]
[TD="align: right"]Thu[/TD]
[TD="align: right"]Fri[/TD]
[TD="align: right"]Sat[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

</tbody>

Lookup​

 

[jsobczak1]

http://[URL=http://s220.photobucket.com/user/jsobczak1/media/Excel/TIMESHEETDETAILS_zpsd0bd1af5.jpg.html]http://i220.photobucket.com/albums/dd175/jsobczak1/Excel/TIMESHEETDETAILS_zpsd0bd1af5.jpg[/IMG[/URL]

Hey sijpie!

Well I worked on amending my sheet to your calculations but the DayNr value isn't consistent in returning the correct number. Any chance you can modify to accommodate the existing formulas? See above link.
Thanks!!!
J

[QUOTE="jsobczak1, post: 3740301, member: 275866"]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 ;)[/QUOTE]

 

[sijpie]

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

 

[jsobczak1]

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: