Help with previous post

[Peter1973]

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.

 

[iliace]

I need a formula to look in Sheet 2 A1 then match that code in Sheet 1 A1:A1000

=MATCH(Sheet2!A1,Sheet1!$A$1:$A$1000,0)

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.

=OFFSET(Sheet1!$A$1,MATCH(Sheet2!A1,Sheet1!$A$1:$A$1000,0)+5,3)

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

I assume you meant Sheet 2.

=OFFSET(Sheet1!$A$1,MATCH(Sheet2!A1,Sheet1!$A$1:$A$1000,0)+5,3)-Sheet2!B1

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.

6 rows down and 5 columns to the right from the code on sheet 1 (another assumption). It'd be something like this:

=IF(AND(OFFSET(Sheet1!$A$1,MATCH(Sheet2!A1,Sheet1!$A$1:$A$1000,0)+5,3)-Sheet2!$B$1>=("21:00"+0),OFFSET(Sheet1!$A$1,MATCH(Sheet2!A1,Sheet1!$A$1:$A$1000,0)+5,3)-Sheet2!$B$1<=("23:00"+0)),OFFSET(Sheet1!$A$1,MATCH(Sheet2!A1,Sheet1!$A$1:$A$1000,0)+6,5),0)

 

[Peter1973]

I have tried this and the way you explained it it should work as that is eactly what I want it to do ( your assumptions were right ) yet it keeps returning #VALUE

 

[Peter1973]

Is there anyone who would be able to assist with this as no matter what I try it keeps returning the #VALUE

 

[Gerald Higgins]

I tried replicating this, partly to educate myself on OFFSET and MATCH, which I'm not very familiar with - thanks iliace for the example !

I was basically able to get Iliace's formula to work without returning a #value error.

Are you sure you've typed it in correctly ?

I got a #VALUE error when the formula referred to sheets that didn't exist. Is it possible you've done something like this ?

 

[Peter1973]

I have amended the formula slightly to match the names of my sheets and locations it is as follows:

=IF(AND(OFFSET('Order Creation'!$C$6,MATCH('NP Per Hour'!$B$5,'Order Creation'!$C$6:$C$4004,0)+5,3)-'NP Per Hour'!$E$5>=("21:00"+0),OFFSET('Order Creation'!$C$6,MATCH('NP Per Hour'!$B$5,'Order Creation'!$C$6:$C$4004,0)+5,3)-'NP Per Hour'!$E$5<=("23:00"+0)),OFFSET('Order Creation'!$C$6,MATCH('NP Per Hour'!$B$5,'Order Creation'!$C$6:$C$4004,0)+6,5),0)

I have replaced Sheet1$A$1 with Order Creation$C$6
Sheet2$A$1 with NP Per Hour$B$5
Sheet1$A$1:$A$1000 with Order Creation $C$6:$C$4004

It is still returning the #VALUE