MATC=ADH(100,INDEX(B2:J5,MATCH(A10,A2:A5,0),)))=3D21/24)*30/1440
by
Thanks for your help Biff
The Formulas work well they now look like this
Start Time
=3DINDEX(StartFinishTimeRange,MATCH(0.5,INDEX(HoursIndex,MATCH(A10,TeamMemb=
ers,
0),),0))
Finish Time
=3DINDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A14,TeamMemb=
ers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A14,TeamMember=
s,
0),)))=3D21/24)*30/1440
I am still needing to refine them though how do I now get this finish
time formula to work for any finish time so I can just copy this
formula to all staff members and it will work regardless the finish
time?
Also when I have a staff member not working all cells are left blank I
need the above formulas not to return #N/A to the start finish time
cells but to instead return a result of text "OFF" or maybe it could
pass " ----" . This is needed because i pass the cells with the start
finish times to a roster summary page which is the roster the staff
read when printed. Formulas on the roster summary currently can handle
I have tried putting a ISNA formula into the formulas but it errors
out and excel won't allow me to save the formula. one of the errors it
may come up with is that the user has restricted the format of the
cell. which I have tried setting as hh:mm AM PM and also as general? I
got to admit I have no real idea what I am doing with the ISNA formula
as the current formula is getting pretty big.
Currently i have tried this with the finish formula haven't tried the
start formula yet some attempts are shown below
=3DIF(ISNA(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A10,=
TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A10,TeamMember=
s,
0),)))=3D21/24)*30/1440,"",INDEX(StartFinishTimeRange,MATCH(100,INDEX(Hours=
Index,MATCH(A10,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A10,TeamMember=
s,
0),)))=3D21/24)*30/1440
it still returns #N/A
also have tried this
=3DIF(ISBLANK(C10:AE10),"",INDEX(StartFinishTimeRange,MATCH(100,INDEX(Hours=
Index,MATCH(A10,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A14,TeamMember=
s,
0),)))=3D21/24)*30/1440)
still returns #N/A
Any ideas
Thanks
Silver Rose