Calculate when Status is New and Recipient has received minimum of 1 of each type of Award
Hello,
I have a Smartsheet for team recognitions, that has column Recipient (dropdown list with approx. ~100 contact names), Recognition Theme (drop down list with 4 themes) and a column for Status (dropdown with ‘New’ and ‘Processed’). I am trying to build a formula to calculate when Status is New and a Recipient has received minimum of 1 of each type of Recognition Theme. When this criteria is met, the answer to be populated is TRUE.
Thank you
Best Answer
-
Hey Paul,
I am using that field for automated alert notification for someone to take next step actions. I don't necessarily need to change the 1 to TRUE or YES or anything else, but because the alert is going to someone else, I thought it might be less confusing for the person receiving the alert to see words instead of numbers. I have added a new column with auto-calculated field with YES/NO response and basing my alert on that. Thank you very much for your help.
Answers
-
Would it be in a separate sheet or in the same sheet?
-
Hey Paul,
It will be in the same sheet.
-
Try something like this:
=IF(COUNT(DISTINCT(COLLECT([Recognition Theme]:[Recognition Theme], Status:Status, @cell = "New", Recipient:Recipient, @cell = Recipient@row))) = 4, 1)
-
Hi Paul,
Thank you. It works!
However, when I tried to replace the 1 with TRUE but getting error #INVALID COLUMN VALUE.
-
Why are you trying to replace the 1 with TRUE? Are you able to provide a screenshot for reference/context?
-
hi Paul,
I have added a workaround column with IF function for YES/NO. Thank you for your help.
-
Hey Paul,
I am using that field for automated alert notification for someone to take next step actions. I don't necessarily need to change the 1 to TRUE or YES or anything else, but because the alert is going to someone else, I thought it might be less confusing for the person receiving the alert to see words instead of numbers. I have added a new column with auto-calculated field with YES/NO response and basing my alert on that. Thank you very much for your help.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!