Help with a formula

[asad]

I got this formula in a worksheet that is supposed to get the result form another workbook I wil post a shot of a portion here.In this BG8:BG15 is named as "Fatigue". The formula in BH9 is supposed to return 15/06/2011 because that's the date when the same person is working one of the shifts numbers mentiooned in range "Fatigue". BUt it returns error. Why?

Excel Workbook
	BF	BG	BH
8	DF143	1	OK
9	DF121	2	#N/A
10	DF022	749
11	DF117	142
12	DF109	143
13	DF060	139
14	DF034	128
15	DF042	145
Weekly Roster

Asad

 

[asad]

There is another problem with this formula. When I change it to

{=MIN(IF('[FSCY 11-06-19.xls]Weekly Roster'![COLOR=red]$E$8:$E$200=E9[/COLOR],IF('[FSCY 11-06-19.xls]Weekly Roster'!$I$8:$AK$200=Fatigue,'[FSCY 11-06-19.xls]Weekly Roster'!$I$5:$AK$5,"OK")))}

, then it works BUT only if I have 1 as value in the corressponding row. If it is 2 or 749 or any other number form the range Fatigue, it returns 1/01/1904.
Just for the information, E8:E200 has exactly same data as in BF column.

Can't understand why?

Asad

 

[asad]

I tried another way using index and match with no luck.

Any suggestions?

 

[asad]

bump.

 

[asad]

{=MIN(IF('[FSCY 11-06-19.xls]Weekly Roster'![COLOR=red]$E$8:$E$200=E9[/COLOR],IF('[FSCY 11-06-19.xls]Weekly Roster'!$I$8:$AK$200=Fatigue,'[FSCY 11-06-19.xls]Weekly Roster'!$I$5:$AK$5,"OK")))}[/CODE[/QUOTE]
 
I think, the formula above will work if I could somehow make it to look for all the values in named range "Fatigue".
 
But I thought that array formulas do that anyway. That means I am wrong. But how?????
 
Asad

 

[asad]

I tried

{=MIN(IF('[FSCY 11-06-19.xls]Weekly Roster'!$E$8:$E$200=E9,IF([COLOR=red][B]OR[/B][/COLOR]('[FSCY 11-06-19.xls]Weekly Roster'!$I$8:$AK$200=Fatigue),'[FSCY 11-06-19.xls]Weekly Roster'!$I$5:$AK$5,"OK")))}

with OR but still no luck.