If/AND/OR Formula for 3 way solution

[Skye123456]

Please refer to attached image. I want the cell titled "current status" aka column D to have 3 rules. I can't get the formula right. This is what I've used:



=IF(AND(B2>0,"Closed","Open"), OR(E2>0,"","open"))

* Column B's cells references another cell on a different tab which is a date. If there is date(B2>0), then D2 equal blank, but if there is a date, D2 equals open. However,

*E Column's cells references a date too. If E2 is blank, then E2 is open. If there is a date, then E2 = closed.

Basically I have upload a picture from excel that has three rows highlighted demonstrating the three scenarios. I don't know if this is all possible or not though.

[Pecoflyer]

If there is date(B2>0), then D2 equal blank, but if there is a date, D2 equals open

This condition is contradictory

E Column's cells references a date too. If E2 is blank, then E2 is open. If there is a date, then E2 = closed.

You mean if E2 blank D2="open", (not E2)?
At first glance
=IF(B2="","",IF(E2="","open","closed"))

[Skye123456]

Yes sorry if E2 is blank.

Your formula half works, but when B2 is blank (no date) D2 says closed instead it should be blank too.

I really appreciate your help though.

[Pecoflyer]

Then B2 is not a real empty cell

As you can see, the first test is to check if B2="" and if so return "", then do nothing else
Perhaps it is not empty but blank ( check with the ISBLANK formula) or contains a hidden value

[Skye123456]

No B2 isn't an empty cell, nor is E2. Both of those cells reference other cells in another tab therefore they have a formula in them. But I need to keep the formula. Your formula works perfectly if they were blank. The idea is the have the spreadsheet completely populate itself from the different tabs.

[Pecoflyer]

Please post a sample file(no pics please) we are going in circles here

[Skye123456]

A sample is attached.

[Pecoflyer]

As you are referring to merged cells in col B ( and maybe others), results can be unpredictable.
Merged cells should be eliminated , being the worst thing ( well almost..) MS invented
When this is done try =IF(B2=0,0,IF(E2="","open","closed"))

[Skye123456]

=IF(B10<1,"",IF(E10>1,"Closed","Open")) worked it out but thanks for all your help