Populate a cell based on data in other cells

osaben

Board Regular
Joined
Mar 17, 2010
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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!


[TABLE="width: 515"]
<tbody>[TR]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"]A
[/TD]
[TD="width: 81, bgcolor: transparent"]B
[/TD]
[TD="width: 81, bgcolor: transparent"]C
[/TD]
[TD="width: 81, bgcolor: transparent"]D
[/TD]
[TD="width: 81, bgcolor: transparent"]E
[/TD]
[TD="width: 145, bgcolor: transparent"]F
[/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]1
[/TD]
[TD="width: 145, bgcolor: transparent"]Build

[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]2
[/TD]
[TD="width: 145, bgcolor: transparent"]Test

[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]3
[/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]4
[/TD]
[TD="width: 145, bgcolor: transparent"]Write

[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"]5
[/TD]
[TD="width: 145, bgcolor: transparent"]Ship

[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 81, bgcolor: transparent"]Yes/No
[/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 470, bgcolor: transparent, colspan: 5"]Use data in columns A-E and place result in column F

[/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 226, bgcolor: transparent, colspan: 2"]If A is missing, Leave F blank

[/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 696, bgcolor: transparent, colspan: 7"]If A is populated and B is "No", then place the text "Stop Here" in column F
[/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 777, bgcolor: transparent, colspan: 8"]If A is populated and B is "Yes" and C, D & E are "Yes", then place the text "Obligation" in column F
[/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 859, bgcolor: transparent, colspan: 9"]If A is populated and B is "Yes" any of C, D & E are "No", then place the text "Not anObligation" in column F
[/TD]
[/TR]
[TR]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 145, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try this in F1 and copy down to other rows:

Code:
=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","")))
 
Upvote 0
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>
 
Upvote 0
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




ABCDEF
YesYesYesYes
TestYesYesYesYesObligation
TestNoYesYesYesStop Here
WriteYesYesNoYesNot anObligation
Ship YesNoYesYesNot 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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

</tbody>

CellFormula
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>
 
Upvote 0
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", ""))))
 
Last edited:
Upvote 0
Try this shorter formula

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

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top