Automatic fill in of drop down box

Help please!

I have a excel spreadsheet which needs some automatic fills

For example in column A I have the following list of names
John
Jo
Jack
James
Peter
Fred
Frank
Sandy

In Column B next to those names I have a drop down list which displays choices next to the names - “yes” or “no”

What I want a formula for is .....if John is selected as “yes” his brother Fred and sister Sandy will also be automatically populated with “yes” from the drop down menu too. If John is “no” I
also want Fred and sandy to be “no” too.

I am lost...

I am a novice so please be kind!!
Thanks!

agggh! Perhaps that was a bad example.... in my real list they will always be the cells just below

here is a new example !

column A has

a (row/coumn A1)
b (a2)
c (a3 etc)
d
e
f
g
h

Column b next to each letter the choice
Yes no


if a is no (or yes) rows b and c will also be no (or yes)

if D is yes (or no) , e will be yes (or no)

if F is yes (or no) , G and H will be yes (or no)

Perhaps another bad example and it doesn't answer your question....

I am hoping the formula inserted into the cell provides the info - for example ....in A2 the idea is that if A1 =yes A2 also = yes (and vice versa) (but it involves a drop down list so IF formula not that simple???? or not even right at all.... (just using it to show what I want to achieve))

thanks !!!