Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Checkbox formula

    ✭✭✭
    in Formulas and Functions

    Hello,

    I've created a checkbox formula to check the box if there is a duplicate value in the IMEI # column. In theory, the formula is working fine for the data already there, but when I enter new "duplicate" row as a test, the formula does not work. The checkbox appears without being checked. Can someone please provide some insight as to why this might be happening? Please see the formula below. Thanks!


    image.png


    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭
      Answer ✓

      Hello Okey,


      Thanks for the suggestion! I was actually able to get it to work with this formula yesterday:

      =IF(COUNTIFS([IMEI #]:[IMEI #], [IMEI #]@row <> " ", [IMEI #]:[IMEI #], [IMEI #]@row) > 1, 1, 0)

      However, if I encounter any issues with it, I will be sure to give the formula you suggested a try!


      Thanks!

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭

      In addition, fyi, I converted the formula to a column formula.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Hi TJE,

      We use a similar process for duplicate submissions and I had the same issue. Change your formula to use > 0, 1 and see if it corrects the issue.


      This is what I used : IF(COUNTIFS(SID:SID, SID@row, Date:Date, <Date@row) > 0, 1)

      MARLANA KALINOWSKI

      Sr. Business Analysts / Smartsheet Solutions

      National Pharmacy Services | Genoa Healthcare

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Hi Marlana,

      Thanks for your assistance! Unfortunately, no it did not work as intended. The modification actually caused additional boxes which are not duplicates to also be checked.

      · Share on FacebookShare on Twitter
    • Community Champion

      Hello @TJE

      I use this solution for flagging duplicate entries - https://community.smartsheet.com/discussion/111316/highlight-duplicate-entries

      Peggy

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Hi Peggy,

      Thanks for your assistance! Yes, the formula worked, however, it returns a "1" when the for the blank cells in that column as well. After modifying the formula you provided as well as the one I provided, I discovered the issue with the highlighted section of the formula (see screenshot). When I do not use it, the formula works but returns a 1 for the blank cells as well. So, in essence, they both work to a degree, but neither provide exactly what is needed.


      image.png

      I will be on vacation starting now and returning next week. I will follow-up on any additional suggestions that are offered once I return.


      Thanks so much Smartsheet Community! :)

      · Share on FacebookShare on Twitter
    • Community Champion

      @TJE

      Try this:

      =IF([IMEI #]@row <> "", IF(COUNTIFS([IMEI #]:[IMEI #], @cell = [IMEI #]@row, helper:helper, @cell <= helper@row) >= 2, 1))

      I created a helper column. This column is an # auto number column with nothing specified.

      Screenshot 2023-11-20 at 3.54.59 PM.png

      This does not check the box for the original entry, just the duplicate entries. I added conditional formatting to highlight the entire row if Checkbox is checked.

      Hope you have a good week away. Let me know.

      Thanks, Peggy

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Hello Peggy!

      Thanks so much for your assistance. Unfortunately, no, it did not work as intended. It checked some IMEI #s that were not duplicates as well.

      It seems the formulas are checking the box even if there are only few numbers in the IMEI number that are the same.


      Thanks.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭
      edited 11/30/23

      Hi @TJE ,

      Try this:

      =IF(ISNUMBER(IMEI@row), COUNTIFS(IMEI:IMEI, IMEI@row) > 1, 1).

      I hope that helps

      · Share on FacebookShare on Twitter
    • ✭✭✭
      Answer ✓

      Hello Okey,


      Thanks for the suggestion! I was actually able to get it to work with this formula yesterday:

      =IF(COUNTIFS([IMEI #]:[IMEI #], [IMEI #]@row <> " ", [IMEI #]:[IMEI #], [IMEI #]@row) > 1, 1, 0)

      However, if I encounter any issues with it, I will be sure to give the formula you suggested a try!


      Thanks!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions