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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!