IF Statement with nested OR

There's the problem. The checkbox column references the Status column and the Status column references the checkbox column.

You will need to remove one of the formulas.

All comments

Hi,

In your Status column try:

=IF(ISDATE(Achived@row), "Red", IF(AND(IS BLANK(Archived@row), [Internal Only]@row=0), "Green", IF(AND(ISBLANK(Archived@row), [Internal Only]@row=1), "Yellow", "")))

Does it do what you wanted?

Mark

It tells me the syntax isn't quite right.

I had a typo:

=IF(ISDATE(Archived@row), "Red", IF(AND(IS BLANK(Archived@row), [Internal Only]@row=0), "Green", IF(AND(ISBLANK(Archived@row), [Internal Only]@row=1), "Yellow", "")))

Mark

Sorry, It is still telling the syntax isn't quite right.

ISBLANK and ISDATE should not have a space after IS. The rest looks good. What error are you getting?

=IF(ISDATE(Archived@row), "Red", IF(AND(ISBLANK(Archived@row), [Internal Only]@row=0), "Green", IF(AND(ISBLANK(Archived@row), [Internal Only]@row=1), "Yellow", "")))

Your [Archived] column needs to be a date column. Your [Internal Only] column needs to be a checkbox.

Mark

@Mark Cronk I really appreciate all your help. Thank you so much.

Ok. I tried it again. I noticed that a couple of my column names didn't match. Someone changed it to Internal Only Use vs. Internal Use, which was causing my 1st issue. It was changed in between the time I posted the original question vs the trying the formula. I fixed those and no longer getting the syntax error message.

Now, the error I get is #Circular Ref in my status column and #blocked in my Internal Use column.

Hi Stephanie,

Interesting. So a #blocked error means one of the cells referenced by the formula has an error. What formula are you using in the [Internal Only Use] column?

The #Circular error means the formula references itself. Your Status formula references [Archived] and [Internal Only Use]. Do one of those columns have a formula in them that references [Status]?

You're close to a solution.

Mark

I had a formula in a couple status boxes that I meant to delete, which I have done down. It changed the error codes I was getting. The goal is to convert the formula to a column formula once I get it working. There are no formulas in the [Internal Only] check box only and [Archived] is only a date.

I took a couple screen shots on my sheet to show what I am getting and the formula I used.

What exactly is in the cell with the circular reference error?

@Mark Cronk We can actually simplify the formula a bit. By using the logic that a nested IF stops on the first true value, we can assume that if it gets to the second IF then then first must be false. That means we can cut out the AND statements where we indicate the Archived column is blank.

=IF(Archived@row <> "", "Red", IF([Internal Only]@row = 0, "Green", "Yellow"))

@Paul Newcome when using your formula suggestion, I get #BLOCKED error in the status column and #CIRCULAR REFERENCE error in the Internal only column.

Right. But what exactly is in the cell with the Circular Reference error?

It is a check box column and it was blank.

This is the formula in the box.

=IF([Status: Live, Internal or Archived]@row = "yellow", 1, 0)

There's the problem. The checkbox column references the Status column and the Status column references the checkbox column.

You will need to remove one of the formulas.

I got it. I removed the check box column and it worked!!!