Sort of like SUMIF and COUNTIF - but return a conditional value

[hr38581]

Sheet_1 has the following in columns A & B:
001 Y
003 N
002 Y
002 N
001 Y
Column A of Sheet_2 has sorted, unique values from col A of Sheet_1:
001
002
003
I'd like a formula for Column B of Sheet_2, that finds every instance of the
value in column A and compares its Column B values in Sheet_1, such that if
all are Y it returns Y, if all are N it returns N, but if some are Y and some
are N it returns Y.

The expected results would be:
001 Y
002 Y
003 N
Any help is greatly appreciated!

 

[Bob Umlas, Excel MVP]

=IF(COUNTIF(Sheet1!$A$1:$A$5,B1)=SUMPRODUCT(--(Sheet1!$A$1:$A$5=B1),--(Sheet1!$B$1:$B$5="N")),"N","Y")
and fill down

 

[hr38581]

This formula yielded a circular reference. Since the formula is in
Sheet2!B1, I changed both instances of B1 to A1.
=IF(COUNTIF(Sheet1!$A$1:$A$5,A1)=SUMPRODUCT(--(Sheet1!$A$1:$A$5=A1),--(Sheet1!$B$1:$B$5="N")),"N","Y")
And it looks like I'm getting the correct results!

Thank you very much!!!