Newbie needs some help with formula

Hi Everyone


Ok, I have a little problem that I’m asking for help with. I have inherited a work timesheet and I’ve done most of the alteration, however, I’m stuck on one part.


Here goes…


On the ‘Front’ sheet if you say insert a shiftcode (these can be seen on ‘shifts’ sheet) all work ok until Friday 24th. If you put in shift 85 it does not populate O32. For some reason, it is pulling data from the ‘backsheet’ for which I cannot overcome due to my own level of knowledge.




The logic that is being used is




=(IFERROR(IF(OR($K32='back sheet '!$G$49,FRONT!$K32='back sheet '!$G$50,FRONT!$K32='back sheet '!$G$51,FRONT!$K32='back sheet '!$G$57),VLOOKUP(FRONT!$K32,'back sheet '!$G$49:$J$57,2,0),IF($K32="Rest Day","",(IF(AND($D32="sat",$I32="b/h"),VLOOKUP($K32,Shifts,4,0),IF(AND($D33="mon",$I33="b/H"),VLOOKUP($K32,Shifts,4,0),IF(AND($I32="b/h",$I33="B/h"),"",(IF($I33="b/h",VLOOKUP($K32,Shifts,10,0),VLOOKUP($K32,Shifts,IF($I32="B/H",7,IF($D32="SAT",5,IF($D32="SUN",7,3))),0))))))))),0))


Can someone please help me

Rich




Attached is the Excel sheet


View attachment Time Sheet (Ceredigion) master.xlsx

TRy this

=IFERROR(
IFERROR(VLOOKUP($K32,'back sheet '!$G$49:$J$57,2,0),
IF($K32="Rest Day","",
IF(AND($D32="sat",$I32="b/h"),VLOOKUP($K32,Shifts,4,0),
IF(AND($D33="mon",$I33="b/H"),VLOOKUP($K32,Shifts,4,0),
IF(AND($I32="b/h",$I33="B/h"),"",
IF($I33="b/h",VLOOKUP($K32,Shifts,10,0),VLOOKUP($K32,Shifts,
IF($I32="B/H",7,
IF($D32="SAT",5,
IF($D32="SUN",7,3))),0))))))),0)