IF Statement with nested OR

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

«1

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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


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

  • Stephanie Tran
    Stephanie Tran ✭✭✭✭

    It tells me the syntax isn't quite right.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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


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

  • Stephanie Tran
    Stephanie Tran ✭✭✭✭

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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


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

  • Stephanie Tran
    Stephanie 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 Cronk
    Mark 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 Tran
    Stephanie 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 Newcome
    Paul 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(Archived@row <> "", "Red", IF([Internal Only]@row = 0, "Green", "Yellow"))

  • Stephanie Tran
    Stephanie 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 Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Stephanie Tran
    Stephanie 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 Newcome
    Paul Newcome ✭✭✭✭✭✭
    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.

  • Stephanie Tran
    Stephanie Tran ✭✭✭✭

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

  • Mark Cronk
    Mark 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!