Checkbox formula
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!
Best 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!
Answers
-
In addition, fyi, I converted the formula to a column formula.
-
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
-
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.
-
Hello @TJE
I use this solution for flagging duplicate entries - https://community.smartsheet.com/discussion/111316/highlight-duplicate-entries
Peggy
-
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.
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! :)
-
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.
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
-
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.
-
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!