Struggling trying to auto check a box if a condition and max date are met

Options

Hey there,

I'm try to check a box only if "Changes Approved" column is checked, and it is the latest (max) date in the "Created " column. I'm new to Smartsheet so I'm struggling a bit to come up with a formula that works. Grr...

Thanks

in advance for your help,

-ryan

Best Answer

Answers

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

    Try something like this...

    =IF(AND([Changes Approved]@row = 1, Created@row = MAX(COLLECT(Created:Created, [Changes Approved]:[Changes Approved], @cell = 1))), 1)

  • ryan harrison
    Options

    Thanks Paul!

    And just to follow up...I have watched several videso on formulas but where should I go to learn more about creating formulas like this? I hate to rely on others being super generous to figure stuff out like this.

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

    I am not sure that there is really anything out there for training on formulas like this. It is basically nesting multiple functions together.


    If we lay out our requirements as a list, it is a little easier to piece together.

    IF

    [Changes Approved]@row is checked

    AND

    Created@row is the most recently checked off date

    then

    check the box.


    [Changes Approved]@row is checked

    is

    [Changes Approved]@row = 1


    To get the second portion, we know we need the most recent date which is where we get the MAX function, but then we need to only COLLECT those dates where the box is checked. Thus the MAX/COLLECT.


    Now that we have established both "logical statements" we can drop them into the AND function which goes in the first portion of the IF statement to say that if both are true then output


    Then we put in the output of 1 to have the box checked.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!