Populate a cell based on data in other cells

[osaben]

Help Please! I am trying to populate column F based of the information in columns A-E. The cells I have labeled as Yes/No will initially be blank and will have a data validation to be either Yes or No.

An ideas are most appreciated!

	A	B	C	D	E	F
1	Build	Yes/No	Yes/No	Yes/No	Yes/No
2	Test	Yes/No	Yes/No	Yes/No	Yes/No
3		Yes/No	Yes/No	Yes/No	Yes/No
4	Write	Yes/No	Yes/No	Yes/No	Yes/No
5	Ship	Yes/No	Yes/No	Yes/No	Yes/No
Use data in columns A-E and place result in column F
If A is missing, Leave F blank
If A is populated and B is "No", then place the text "Stop Here" in column F
If A is populated and B is "Yes" and C, D & E are "Yes", then place the text "Obligation" in column F
If A is populated and B is "Yes" any of C, D & E are "No", then place the text "Not anObligation" in column F

<tbody>
</tbody>

 

[The_Macrotect]

Try this in F1 and copy down to other rows:

=IF(A2="","Stop Here",IF(AND(A2<>"",B2="Yes",C2="Yes",D2="Yes",E2="Yes"),"Obligation",IF(AND(A2<>"",B2="Yes",OR(C2="No",D2="No",E2="No")),"Not an Obligation","")))

 

[DanteAmor]

Try this

<br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:39.92px;" /><col style="width:49.43px;" /><col style="width:49.43px;" /><col style="width:49.43px;" /><col style="width:49.43px;" /><col style="width:108.36px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >Yes</td><td >Yes</td><td >Yes</td><td >Yes</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Test</td><td >Yes</td><td >Yes</td><td >Yes</td><td >Yes</td><td >Obligation</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Test</td><td >No</td><td >Yes</td><td >Yes</td><td >Yes</td><td >Stop Here</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Write</td><td >Yes</td><td >Yes</td><td >No</td><td >Yes</td><td >Not anObligation</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Ship </td><td >Yes</td><td >No</td><td >Yes</td><td >Yes</td><td >Not anObligation</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >=IF(A2="","",IF(B2="No","Stop Here",IF(AND(B2="Yes",C2="Yes",D2="Yes",E2="Yes"),"Obligation",IF(AND(B2="Yes",OR(C2="No",D2="No",E2="No")),"Not anObligation"))))</td></tr></table></td></tr></table>

 

[osaben]

Thanks for your help DanteAmor!!

I think the solution is very close. The only problem left is that column F returns the answer "FALSE" when any of the cells in columns B-E are missing. These cells will initially be blank until they are populated via a drop down list.

Any ideas?

Try this

	A	B	C	D	E	F
1
2		Yes	Yes	Yes	Yes
3	Test	Yes	Yes	Yes	Yes	Obligation
4	Test	No	Yes	Yes	Yes	Stop Here
5	Write	Yes	Yes	No	Yes	Not anObligation
6	Ship	Yes	No	Yes	Yes	Not anObligation

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 39.92px;"><col style="width: 49.43px;"><col style="width: 49.43px;"><col style="width: 49.43px;"><col style="width: 49.43px;"><col style="width: 108.36px;"></colgroup><tbody>
</tbody>

Cell Formula F2 =IF(A2="","",IF(B2="No","Stop Here",IF(AND(B2="Yes",C2="Yes",D2="Yes",E2="Yes"),"Obligation",IF(AND(B2="Yes",OR(C2="No",D2="No",E2="No")),"Not anObligation")))) <tbody> </tbody>

<tbody>
</tbody>

 

[DanteAmor]

Try

=IF(A2="","",IF(B2="No","Stop Here",IF(AND(B2="Yes",C2="Yes",D2="Yes",E2="Yes"),"Obligation",IF(AND(B2="Yes",OR(C2="No",D2="No",E2="No")),"Not anObligation", ""))))

 

[DanteAmor]

Try this shorter formula

=IF(A2="","",IF(B2="No","Stop Here",IF(B2="Yes",IF(COUNTIF(C2:E2,"Yes")=3,"Obligation","Not anObligation"),"")))