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


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...


in advance for your help,


Best Answer


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

    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

    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 ✭✭✭✭✭✭

    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.


    [Changes Approved]@row is checked


    Created@row is the most recently checked off date


    check the box.

    [Changes Approved]@row is checked


    [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!