Blank cells to trigger an uncheck mark in other column

Options
EllanaV
EllanaV ✭✭
edited 12/07/23 in Formulas and Functions

I am fairly new to using intricate formulas in smarsheet as a headsup to a probably easy fix. I wanted to be able to make blank/ unchecked columns when there isnt any date or risk status mark in my other columns. Right now I have a risk status column that depends on dates in the Abstract Due Date column. The Done column depends on the grey ball in Risk Status, meaning a passed due Abstract Due Date. If the abstract has passed, the done status is Checked. (I havent set up if it hasnt passed, there is no check mark and thats probably part of the problem). Here are the formulas to hopefully make this clear

Risk Status: =IF([Abstract Due Date]@row >= TODAY(60), "Green", IF([Abstract Due Date]@row >= TODAY(30), "Yellow", IF([Abstract Due Date]@row >= TODAY(1), "Red", IF([Abstract Due Date]@row <= TODAY(), "Gray"))))

Done: =IF([Risk Status]@row = "Gray", 1)

What should I set up in the done column formula that wont trigger a check mark when an abstract hasnt past yet. Also I want both columns to not do anything if there isnt any date in the Abstract Due Date column. I tried a IFBLANK formula, but keep getting "unparseable"


Answers

  • Lauren Kleitz
    Lauren Kleitz ✭✭✭✭
    Options

    You are on the right track here! What you need to add is a condition using the ISBLANK formula. This formula slots into the condition block of an IF statement for example the formula below would set a checkbox to unchecked if the abstract due date column was blank:

    =IF(ISBLANK([Abstract Due Date]@row), 0, 1)

  • EllanaV
    EllanaV ✭✭
    Options

    For existing rows that have the "none" clicked in the Abstract Due Date, it doesnt trigger the check mark or grey ball to go away. And it wont let me manually turn it off either since the formula is in there. I would like for these two columns(risk and done) to be empty when there is no date in the Abstract Due Date.


    Thank you so much for your quick response!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!