Matching Daily Attendance to a Master List of Badge Numbers



I am attempting to convert a Conditional Formatting formula from Excel into a smartsheet by using a Helper column.

If a badge is scanned, I want the number to populate in a "Daily Scans" column, then I want to match that against a Master List of Badge numbers "Badge Numbers"

My Helper column formula is: =IF([Daily Scans]@row = [Badge Number]:[Badge Number], "Green") but I receive an #INVALID OPERATION in my Helper column.

My original Excel conditional formatting formula is =COUNTIF($C$C, $B1)>0 where C is the "Daily Scans" and B is the Badge Number.

Any input would be appreciated.



  • James Keuning
    James Keuning ✭✭✭✭✭

    I am not 100% sure what you are trying to accomplish, but I think you can use COUNTIFS.

    =IF(COUNTIFS([Badge Number]:[Badge Number], [Daily Scans]@row) >= 1, "green")

  • Seth Hennes

    Thanks for the response. The goal is to highlight those Employees who are in attendance for the day.

    The Columns are:

    Name Badge Number Daily Scan

    Jon 12345 00001

    Sally 54321

    Tom 00001

    The Daily Scan column would be populated by a USB barcode scanner and would fill up as employees arrive. When a scanned badge matches any Badge Number in the Column then the name would highlight indicating that “Tom” was in attendance (in the above example).

    I am able to do this in Excel, I’m just not familiar enough with Smartsheets to translate the conditional formatting



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!