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.

Attached Images

Excel 1.JPG (70.2 KB, 27 views)

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

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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.

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

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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.

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

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

A sample is attached.

Attached Files

sample.xlsx (56.5 KB, 11 views)

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"))

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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