Status should = "Pending" if the cell is null

BJIloveSmartSheet

I have a sheet that looks for errors in a field, then populates a status if it finds those errors. For example:

=IF(LEN(Body@row) < 1, "RED", IF(OR(CONTAINS("Rejected: 0", Body@row)) = true, "Green", "Yellow"))

However, if the cell is null, it reads "#INVALID VALUE". In addition to the formula above, I would like to include if the cell is null, status is "Pending".

How would I do that? Thanks so much.

Ray Lindstrom

@BJIloveSmartSheet,

If by null you mean blank, then try this:

=IF(ISBLANK(Body@row), "Pending", IF(LEN(Body@row) < 1, "RED", IF(OR(CONTAINS("Rejected: 0", Body@row)) = true, "Green", "Yellow")))

I only added the "if blank" part to the beginning. It looks like the rest of the formula could use a little work. The IF(LEN(Body@row) < 1 is saying if there are 0 characters in the Body field. Because of this, you will never see "Red" listed. "is blank" and 0 characters means the same thing.

When do you want "Red" to be listed? I can update the formula to help capture that if you would like.

Hope that helps!

BRgds,

-Ray

Ray Lindstrom

@BJIloveSmartSheet,

If by null you mean blank, then try this:

=IF(ISBLANK(Body@row), "Pending", IF(LEN(Body@row) < 1, "RED", IF(OR(CONTAINS("Rejected: 0", Body@row)) = true, "Green", "Yellow")))

I only added the "if blank" part to the beginning. It looks like the rest of the formula could use a little work. The IF(LEN(Body@row) < 1 is saying if there are 0 characters in the Body field. Because of this, you will never see "Red" listed. "is blank" and 0 characters means the same thing.

When do you want "Red" to be listed? I can update the formula to help capture that if you would like.

Hope that helps!

BRgds,

-Ray