Check box on main sheet if AT LEAST one row matches on another

jeannaplummer ✭✭✭
edited 06/14/24 in Formulas and Functions

I have been searching and struggling to find a solution for this. I have a test execution sheet that I want a flag (checkbox) to automatically check if an issue is logged on a separate issue sheet that matches the Test ID number. The problem I'm running into now is that if we log more than one for that Test ID, the flag cell errors. How do I fix this so that it just checks the box as long as at least one row matches?

Test Execution Sheet (I have a link set up for each row so that the form automatically pulls in the Test ID) - REC EF Issue Log ID is the TestScenarioStepID column in the Issue Log and Rec/EF NAMEDID is the first cell of that column

Issue Log

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @jeannaplummer

    I believe the COUNTIFS in your screenshot is missing one criteria- a COUNTIFS always needs ranges to be paired with criteria.

    Will this work?

    IF(COUNTIFS({REC EF Issue Log ID},TestScenarioStepID@row)>0, 1)

    This looks for 1 or above as the count.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!