I have this formula in row I3:
=IF(AND(G3='Daily Report'!C6,H3='Daily Report'!$B$7,'Daily Report'!C7=TRUE),"Present",IF(AND(G3='Daily Report'!C6,H3='Daily Report'!$B$7,'Daily Report'!C7=FALSE),"Absent",""))
I need it to change the Column references when i drag it down, like:
=IF(AND(G4='Daily Report'!D6,H4='Daily Report'!$B$7,'Daily Report'!D7=TRUE),"Present",IF(AND(G4='Daily Report'!D6,H4='Daily Report'!$B$7,'Daily Report'!D7=FALSE),"Absent",""))
=IF(AND(G5='Daily Report'!E6,H5='Daily Report'!$B$7,'Daily Report'!E7=TRUE),"Present",IF(AND(G5='Daily Report'!E6,H5='Daily Report'!$B$7,'Daily Report'!E7=FALSE),"Absent",""))
=IF(AND(G6='Daily Report'!F6,H6='Daily Report'!$B$7,'Daily Report'!F7=TRUE),"Present",IF(AND(G6='Daily Report'!F6,H6='Daily Report'!$B$7,'Daily Report'!F7=FALSE),"Absent",""))
as always, your support is much appreciated.
Regards,
This formula uses the OFFSET function to shift the column reference based on the row number (untested but the idea should be sound):
=IF(AND(G3=OFFSET('Daily Report'!$C$6,0,ROW()-3),
H3='Daily Report'!$B$7,
OFFSET('Daily Report'!$C$7,0,ROW()-3)=TRUE),
"Present",
IF(AND(G3=OFFSET('Daily Report'!$C$6,0,ROW()-3),
H3='Daily Report'!$B$7,
OFFSET('Daily Report'!$C$7,0,ROW()-3)=FALSE),"Absent",""))