Compare two sheets; flag a name that has not submitted a form


I'm trying to flag individuals who have NOT submitted a form, but am coming up short on the formula.

Sheet A Memberlist has a list of all the individuals that need to submit the form.

Sheet B Feedback is the form data after it has been submitted.

I want to use Sheet A Memberlist as the reference for who is supposed to submit a form for a specific session, in this case the FDM session.

The formula I'm trying to use is in a toggled Flag column and states:

=IF(FDM@row = 0, 0, IF(COUNTIFS({B Feedback Range 1}, Name@row, {B Feedback Range 2}, <>"FDM", 0, 1)))

Where FDM is a checkbox indicating whether or not the individual should submit.

{B Feedback Range 1} is the column of names on the form data.

{B Feedback Range 2} is the column of sessions, in this case the FDM session.

I'm getting an INCORRECT ARGUMENT SET error. Help!


Best Answer

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

    Hey @John C

    Try this approach. This uses the Countifs to determine how many instances can be found where the name matches the FDM session. If the count result is zero, the person has not submitted.

    =IF(FDM@row = 0, 0, IF(COUNTIFS({B Feedback Range 1}, Name@row, {B Feedback Range 2}, "FDM")=0, 0, 1))

    Does this work for you?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!