IF Statement with nested OR

05/28/21
Accepted

I am trying to write a formula and I am not entirely sure if it is possible. I have 3 columns, Status, Internal only, and Archived. I am trying to write a formula for the status column to mark it Green, Yellow or Red.

If the archived column is blank and the Internal Only column is unchecked, I need it green.

If the archived column is blank and the Internal Only column is checked, I need it yellow.

If the archived column has a date, I need it red.


Is this possible?

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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.

    thinkspi.com

Previous1

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi,

    In your Status column try:

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

    Does it do what you wanted?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Stephanie TranStephanie Tran ✭✭✭✭✭

    It tells me the syntax isn't quite right.

  • Mark CronkMark Cronk ✭✭✭✭✭

    I had a typo:

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Stephanie TranStephanie Tran ✭✭✭✭✭

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

  • Mark CronkMark Cronk ✭✭✭✭✭

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

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

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Stephanie TranStephanie Tran ✭✭✭✭✭

    @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.

  • Mark CronkMark Cronk ✭✭✭✭✭

    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'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Stephanie TranStephanie Tran ✭✭✭✭✭

    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.


  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 06/03/21

    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([email protected] <> "", "Red", IF([Internal Only]@row = 0, "Green", "Yellow"))

    thinkspi.com

  • Stephanie TranStephanie Tran ✭✭✭✭✭

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

  • Stephanie TranStephanie Tran ✭✭✭✭✭
    edited 06/03/21

    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)

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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.

    thinkspi.com

  • Stephanie TranStephanie Tran ✭✭✭✭✭

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

  • Mark CronkMark Cronk ✭✭✭✭✭

    Thanks for the assist @Paul Newcome . Well done. Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Sign In or Register to comment.