IF Contains Nested Formula Error

Options

Hello!

Would anybody be able to see what I'm doing wrong here? I'm trying to make it so if my "Part Number" column contains "Purchase Order" that the green icon doesn't show up.


Here's my formula:


=IF(Status@row = "RFQ", "🔴", IF(Status@row = "Ordered", "🟧", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "🟪", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "🎨", IF(Status@row = "Hold", "🛑", IF(Status@row = "Cancelled", "🛑", IF(Status@row = "Order Confirmed", "🔷", IF(Status@row = "Reference Only", "🟦", IF(Status@row = "", "🟢", IF(CONTAINS("Purchase Order", [Part Number]:[Part Number]), "")))))))))))))

I'm just trying to make the formatting look nicer. Also if I filter for the green circle in a report I don't want to see those rows.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @AnnieR Not quite. Each CONTAINS function should be closed independently. This means that one of your bold closing parenthesis should be moved to close out the "Credit Card" CONTAINS.

    =IF(Status@row = "RFQ", "🔴", IF(Status@row = "Ordered", "🟧", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "🟪", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "🎨", IF(Status@row = "Hold", "🛑", IF(Status@row = "Cancelled", "🛑", IF(Status@row = "Order Confirmed", "🔷", IF(Status@row = "Reference Only", "🟦", IF(OR(CONTAINS("Purchase Order", [Part Number]@row), CONTAINS("Credit Card", [Part Number]@row), CONTAINS("Stock Items", [Part Number]@row)), "", IF(Status@row = "", "🟢"))))))))))))


    Think of it this way:

    OR(argument1, argument2, argument3)


    CONTAINS("A", Column@row)

    CONTAINS("B", Column@row)

    CONTAINS("C", Column@row)


    You would then drop each of the CONTAINS functions into an "argument" section of the OR function like so:

    OR(CONTAINS("A", Column@row), CONTAINS("B", Column@row), CONTAINS("C", Column@row))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try changing it from a column reference

    [Part Number]:[Part Number]

    to a cell reference the same way you referenced your Status

    [Part Number]@row

  • AnnieR
    AnnieR ✭✭
    Options

    Thank you @Paul Newcome ! Unfortunately that didn't work.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide the exact (new) formula as well as a screenshot of a row containing "Purchase Order" in the [Part Number] column?

  • AnnieR
    AnnieR ✭✭
    Options

    @Paul Newcome


    Yes! :)


    =IF(Status@row = "RFQ", "🔴", IF(Status@row = "Ordered", "🟧", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "🟪", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "🎨", IF(Status@row = "Hold", "🛑", IF(Status@row = "Cancelled", "🛑", IF(Status@row = "Order Confirmed", "🔷", IF(Status@row = "Reference Only", "🟦", IF(Status@row = "", "🟢", IF(CONTAINS("Purchase Order", [Part Number]@row), "")))))))))))))



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Oh. Your argument immediately before is to output the Green Ball if the Status cell is empty. Try switching those two around so the IF(CONTAINS(.....), "", portion comes before the Status is blank portion.

  • AnnieR
    AnnieR ✭✭
    Options

    Darnit sorry @Paul Newcome I don't know how I'm mixing this up. I also need to add in to be blank if "Credit Card" is in the Part Number column but I can't even get this to work.


    =IF(Status@row = "RFQ", "🔴", IF(Status@row = "Ordered", "🟧", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "🟪", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "🎨", IF(Status@row = "Hold", "🛑", IF(Status@row = "Cancelled", "🛑", IF(Status@row = "Order Confirmed", "🔷", IF(Status@row = "Reference Only", "🟦", IF(CONTAINS("Purchase Order", [Part Number]@row), " "), IF(Status@row = "", "🟢"))))))))))))

    Now I'm just getting an Incorrect Arguement Error.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    The error message is coming from a misplaced closing parenthesis before the final IF. Here it is removed.


    =IF(Status@row = "RFQ", "🔴", IF(Status@row = "Ordered", "🟧", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "🟪", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "🎨", IF(Status@row = "Hold", "🛑", IF(Status@row = "Cancelled", "🛑", IF(Status@row = "Order Confirmed", "🔷", IF(Status@row = "Reference Only", "🟦", IF(CONTAINS("Purchase Order", [Part Number]@row), "", IF(Status@row = "", "🟢"))))))))))))


    To factor in the "Credit Card" bit, we would add an OR statement to the blank output like so:

    =IF(Status@row = "RFQ", "🔴", IF(Status@row = "Ordered", "🟧", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "🟪", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "🎨", IF(Status@row = "Hold", "🛑", IF(Status@row = "Cancelled", "🛑", IF(Status@row = "Order Confirmed", "🔷", IF(Status@row = "Reference Only", "🟦", IF(OR(CONTAINS("Purchase Order", [Part Number]@row), CONTAINS("Credit Card", [Part Number]@row)), "", IF(Status@row = "", "🟢"))))))))))))

  • AnnieR
    AnnieR ✭✭
    Options

    @Paul Newcome Thank you!!!! That worked! The only weird thing was it didn't work if I just copy pasted it in. I had to hand key it back in, which I thought was pretty odd. It was giving me a syntax error if I just copy pasted.

    If I wanted to add in a few more items for the "Purchase Order" or "Credit Card" bits. Like "Stock Items" would this be right?

    =IF(Status@row = "RFQ", "🔴", IF(Status@row = "Ordered", "🟧", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "🟪", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "🎨", IF(Status@row = "Hold", "🛑", IF(Status@row = "Cancelled", "🛑", IF(Status@row = "Order Confirmed", "🔷", IF(Status@row = "Reference Only", "🟦", IF(OR(CONTAINS("Purchase Order", [Part Number]@row), CONTAINS("Credit Card", [Part Number]@row, CONTAINS("Stock Items", [Part Number]@row))), "", IF(Status@row = "", "🟢"))))))))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @AnnieR Not quite. Each CONTAINS function should be closed independently. This means that one of your bold closing parenthesis should be moved to close out the "Credit Card" CONTAINS.

    =IF(Status@row = "RFQ", "🔴", IF(Status@row = "Ordered", "🟧", IF(Status@row = "Received", "☑️", IF(Status@row = "Partial", "🟪", IF(Status@row = "Stock", "☑️", IF(Status@row = "Ready at Vendor", "⏺️", IF(Status@row = "@ S.O. Vendor", "🎨", IF(Status@row = "Hold", "🛑", IF(Status@row = "Cancelled", "🛑", IF(Status@row = "Order Confirmed", "🔷", IF(Status@row = "Reference Only", "🟦", IF(OR(CONTAINS("Purchase Order", [Part Number]@row), CONTAINS("Credit Card", [Part Number]@row), CONTAINS("Stock Items", [Part Number]@row)), "", IF(Status@row = "", "🟢"))))))))))))


    Think of it this way:

    OR(argument1, argument2, argument3)


    CONTAINS("A", Column@row)

    CONTAINS("B", Column@row)

    CONTAINS("C", Column@row)


    You would then drop each of the CONTAINS functions into an "argument" section of the OR function like so:

    OR(CONTAINS("A", Column@row), CONTAINS("B", Column@row), CONTAINS("C", Column@row))

  • AnnieR
    AnnieR ✭✭
    Options

    I'm sorry for my late reply @Paul Newcome !! This worked perfectly!! Thank you for teaching me too. I really appreciate it. Happy Friday and hope you have a good weekend :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!