searching column for value to do checkbox
I'm trying to set up notifications for milestones that my team hits, looking for a specific ticket # in a column to send a notification when this ticket # is hit (for example, "Hey team, we just closed our 150,000th ticket. Amazing work, everybody!!"). I have tried using a checkbox column with formulas like the below without success:
- =IF([Issue Key]:[Issue Key] = "MD-150000", 1, 0)
- In the above, "Issue Key" is the column I'm looking to find the specific value within.
- =IF([Column5] = "MD-150000", 1, 0) / =IF([Column5]1 = "MD-150000", 1, 0)
- I found an "answer" on this space showing a way to look at a "Column" but this hasn't worked for me either (I'm wondering if I'm maybe just looking at the wrong 'column' -> "Issue Key" is the first column in my sheet with data, would this be "Column1", and should you be able to say "look in this column" to find a value?
When I look for a value in a specific cell, it does work; like:
=IF([Issue Key]486 = "MD-150000", 1, 0)
Any help that folks are able to provide will be greatly appreciated.
Thanks!
Comments
-
Try this...
=IF(COUNTIF([Issue Key]:[Issue Key], "MD-150000") = 1, 1, 0)
What this does is first counts all occurrences of MD-150000 within that column then, if the count = 1, then it will check your box.
EDIT:
You can also set up a notification to automatically send whenever a cell within a specific row changes to specific criteria. See screenshots below.
-
Hey Paul, this is great! Thanks for the help. I have one question on the auto-notifications (your second option):
Data in my spreadsheet is pulling in from another source (via an integration with JIRA): Will that auto-notification work whenever data from JIRA pulls in and has a value that matches the criteria I set? I'm assuming so, just want to confirm before I go that route.
Thanks again!
-
Yes. I just tested it and had the notification sent to myself. It works even if it is a cell link.
Help Article Resources
Categories
Check out the Formula Handbook template!
