I am sorry. I meant

a name is assigned either an "M" or "A" in sheet 1 column D

The formula should follow the following sequence

Row 4: =IF(D4="M",IF(SUM('4'!$O$32:$O$42)>=5,"Yes",IF('4'!$O$32>=1,"Yes","No")))

Row 5: =IF(D5="M",IF(SUM('5'!$O$32:$O$42)>=5,"Yes",IF('5'!$O$32>=1,"Yes","No")))

Row 6: =IF(D6="M",IF(SUM('6'!$O$32:$O$42)>=5,"Yes",IF('6'!$O$32>=1,"Yes","No")))

etc (will follow sequence to row 50.)