searching column for value to do checkbox

Jon_Beattie

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!

Paul Newcome

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.

Jon_Beattie

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!

Paul Newcome

Yes. I just tested it and had the notification sent to myself. It works even if it is a cell link.