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...
Thanks
in advance for your help,
-ryan
Best Answer
-
Try something like this...
=IF(AND([Changes Approved]@row = 1, Created@row = MAX(COLLECT(Created:Created, [Changes Approved]:[Changes Approved], @cell = 1))), 1)
Answers
-
Try something like this...
=IF(AND([Changes Approved]@row = 1, Created@row = MAX(COLLECT(Created:Created, [Changes Approved]:[Changes Approved], @cell = 1))), 1)
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!