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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 07/06/18

    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.

    Untitled.png

    Untitled2.png

    Untitled3.png

    Untitled4.png

  • 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
    Paul Newcome ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!