IF Statement for automatic checkbox

Amy Bedard

Hello. I have two columns "Gate Status" and "Gate Current (Mdays)". I'm trying to write a formula that does automatically adds a checkbox to "CurrentGateLate" column when the following happens. When "Gate Status" = "Gate 1" and "Gate Current (Mdays)" is greater than 10, then add checkbox. And....When "Gate Status" = "Gate 2" and "Gate Current (Mdays)" is greater than 30, then add checkbox. And...When "Gate Status" = "Gate 3" and "Gate Current (Mdays)" is greater than 3, then add checkbox. etc.

I can get it to work when only to the first one but when I start to nest, I get #UNPARSAEABLE.

Thank you in advance.

Amy

Bassam Khalil

Hi @Amy Bedard

Hope you are fine, please try the following formula and convert it to a column format formula:

=IFERROR(IF(AND([Gate Status 2]@row = "Gate 1", [Gate Current (Mdays)]@row <= 10), 1, IF(AND([Gate Status 2]@row = "Gate 2", [Gate Current (Mdays)]@row <= 30), 1, IF(AND([Gate Status 2]@row = "Gate 3", [Gate Current (Mdays)]@row <= 3), 1, IF(AND([Gate Status 2]@row = "Gate 4a", [Gate Current (Mdays)]@row <= 30), 1)))), "")

the following screenshot shows the result:

Andrée Starå

Hi @Amy Bedard

I hope you're well and safe!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots and the formula you're trying to get working? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Amy Bedard

Thank you for your response.

Right now, the sheet was set up to manually check the box in "CurrentGateLate" when the row (Item number is Late). As I improve on my Smartsheet skills :-), I know that there has to be a formula to check it for me. What makes it Late varies on the column "Gate Status 2" (and I guess adding another challenge for me that the "Project Status" is Open...but I'm not as worried about that).

When Gate Status 2 = "Gate 1" then they only have 10 Mdays before it goes Late, requiring check.

When Gate Status 2 = "Gate 2" then they only have 30 Mdays before it goes Late, requiring check.

When Gate Status 2 = "Gate 3" then they only have 3 Mdays before it goes Late, requiring check.

When Gate Status 2 = "Gate 4a" then they only have 30 Mdays before it goes Late. (the example is Gate 4a but not yet late.

Bassam Khalil

Hi @Amy Bedard

Hope you are fine, please try the following formula and convert it to a column format formula:

=IFERROR(IF(AND([Gate Status 2]@row = "Gate 1", [Gate Current (Mdays)]@row <= 10), 1, IF(AND([Gate Status 2]@row = "Gate 2", [Gate Current (Mdays)]@row <= 30), 1, IF(AND([Gate Status 2]@row = "Gate 3", [Gate Current (Mdays)]@row <= 3), 1, IF(AND([Gate Status 2]@row = "Gate 4a", [Gate Current (Mdays)]@row <= 30), 1)))), "")

the following screenshot shows the result:

Amy Bedard

Thanks. That worked. I needed it to be ">" instead of "<=", but that was easy enough to change without messing it up. I appreciate the help.

Andrée Starå

@Amy Bedard

Happy to help!

I saw that Bassam answered already!

Let me know if I can help with anything else!

Best,

Andrée