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

ryan harrison

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

Paul Newcome

Try something like this...

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

Paul Newcome

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

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.