Using "IF" and "AND"

[paulwatson5]

Hi guys, to stop me banging my head against the wall any more, could you please help me out on a formula?

I have 2 columns (O & P) that have either blanks or dates.
In Column "S" I want a formula in there that will bring back one of 3 possible outcomes.

If O is blank and P has a date, return "Review Complete"
If O has a date and P has a date, return "Review Complete" (same outcome as above)
If O has a date and P is blank, return "On Hold"
If O is blank and P is also blank, return "In Progress"

Could anyone help me out here please??

Thanks!

 

[mandukes]

That one didn't work, it just populated every cell as "Review Complete"

The whole criteria is at yesterdays message plus the extra bit which in total is:

If O is blank and P has a date, return "Review Complete"
If O has a date and P has a date, return "Review Complete" (same outcome as above)
If O has a date and P is blank, return "On Hold"
If O is blank and P is also blank, return "In Progress" (might have to put in that L is non-blank?)
If O is blank and P is blank and L is also blank "Not Started"

Try this.. friend :D

=IF(AND(L7="",O7="",P7=""),"Not Started",IF(AND(O7="",P7<>""),"Review Complete",IF(AND(O7<>"",P7<>""),"Review Complete",IF(AND(O7<>"",P7=""),"ON HOLD",IF(AND(O7="",P7=""),"In Progress","")))))

 

[FormR]

That one didn't work, it just populated every cell as "Review Complete"

Here it is in action, are you sure you implemented it correctly?

Excel 2013/2016
	L	M	N	O	P	Q
2				08/12/2017	08/12/2017	Review Complete
3					08/12/2017	Review Complete
4				08/12/2017		On Hold
5	Something					In Progress
6						Not Started
Sheet1

Cell Formulas
Range	Formula
Q2	=IF(ISNUMBER(P2),"Review Complete",IF(ISNUMBER(O2),"On Hold",IF(L2="","Not Started","In Progress")))

 

[paulwatson5]

Try this.. friend :D

=IF(AND(L7="",O7="",P7=""),"Not Started",IF(AND(O7="",P7<>""),"Review Complete",IF(AND(O7<>"",P7<>""),"Review Complete",IF(AND(O7<>"",P7=""),"ON HOLD",IF(AND(O7="",P7=""),"In Progress","")))))

Thanks for this, the date in cells L, O & P are linked from another spreadsheet. So if there is nothing in the source spreadsheet cell, it is coming through as "0" in my spreadsheet. Everything that should be "Not Started" is still showing at "In Progress"

 

[paulwatson5]

Here it is in action, are you sure you implemented it correctly?

Excel 2013/2016

	L	M	N	O	P	Q
Review Complete
On Hold
Something	In Progress
Not Started

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]08/12/2017[/TD]
[TD="align: right"]08/12/2017[/TD]
[TD="bgcolor: #FFFF00"]Review Complete[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]08/12/2017[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]08/12/2017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q2[/TH]
[TD="align: left"]=IF(ISNUMBER(P2),"Review Complete",IF(ISNUMBER(O2),"On Hold",IF(L2="","Not Started","In Progress")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes, implemented it correctly, I just don't think it covers the whole scenario.

 

[FormR]

I just don't think it covers the whole scenario.

So if there is nothing in the source spreadsheet cell, it is coming through as "0" in my spreadsheet

Hi, I don't think you told us the whole story, a zero is not the same as a blank. What does column L have - zero's or blanks?

 

[mandukes]

Thanks for this, the date in cells L, O & P are linked from another spreadsheet. So if there is nothing in the source spreadsheet cell, it is coming through as "0" in my spreadsheet. Everything that should be "Not Started" is still showing at "In Progress"

Just add this to Cell O, L, P cells

Here, replace it with your sheet name and cell where the data is linked from

=IF(Sheet3!D8="","",Sheet3!D8)

 

[snjpverma]

Try this if L2, O2 and P2 are all dates/numbers.

=IF(O2+P2+L2,IF(O2+P2,IF(P2,"Review Complete","On Hold"),"In Progress"),"Not Started")

 

[paulwatson5]

Just add this to Cell O, L, P cells

Here, replace it with your sheet name and cell where the data is linked from

Yes, that's the fella!!! Thanks so much everyone.