IF Contains Nested Formula Error

AnnieR

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.

Paul Newcome

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

Paul Newcome

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

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

Paul Newcome

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

@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

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

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

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

@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

@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

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

Paul Newcome

@AnnieR Happy to help. 👍️