Populate a cell based on data in other cells

O

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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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?

DanteAmor said:
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>
Click to expand...
 
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
You must log in or register to reply here.

Similar threads

A
  • Solved
Conditional Formatting - apply rules to multiple rows based on specific cells (drop down menus)
Replies
4
Views
759
AndyB86
A
T
  • Question Question
Automated drop down based on 1st selection + other column data
Replies
5
Views
358
Timber5
T
S
  • Solved
Cell Formatting with Text Data
Replies
2
Views
70
SBulinski1975
S
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
315
cokeoverpepsi123
C
willow1985
  • Solved
IF AND formula, returns "Yes" if column matches a cell reference and 2nd column starts with first 3 characters in a reference list
Replies
6
Views
255
Cubist
Cubist

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

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