I previously asked for help but post was not clear hope this can explain it correctly .
In sheet 1 I have :
In cells A1:A1000 I have various codes. General format ie G2004
In Cells B1:B1000 I have various times. In hh:mm format
In Cells C1:C1000 I have various days. In ddd format.
In sheet 2 I have :
In cells A1 I have a code.
In cells B1 have a time in hh:mm format.
I need a formula to look in Sheet 2 A1 then match that code in Sheet 1 A1:A1000, then once it has found the code look in the cell that is 3 colums to the right and 5 rows down from the code. Then in that cell will be a time i need it then to subtract sheet 1 B1 from that time ( ie 23:23 - B1 if B1 was 0:20 answer would be 23:03 ).
Then iff possible if that time between 21:00 and 23:00 return the value in the cell 5 columns to the right and 6 rows down from the code. Otherwise return zero.
Hope this is understandable as it is killing me thinking about it.
In sheet 1 I have :
In cells A1:A1000 I have various codes. General format ie G2004
In Cells B1:B1000 I have various times. In hh:mm format
In Cells C1:C1000 I have various days. In ddd format.
In sheet 2 I have :
In cells A1 I have a code.
In cells B1 have a time in hh:mm format.
I need a formula to look in Sheet 2 A1 then match that code in Sheet 1 A1:A1000, then once it has found the code look in the cell that is 3 colums to the right and 5 rows down from the code. Then in that cell will be a time i need it then to subtract sheet 1 B1 from that time ( ie 23:23 - B1 if B1 was 0:20 answer would be 23:03 ).
Then iff possible if that time between 21:00 and 23:00 return the value in the cell 5 columns to the right and 6 rows down from the code. Otherwise return zero.
Hope this is understandable as it is killing me thinking about it.