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

John C

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!

Kelly Moore

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?

cheers

Kelly Moore

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?

cheers

John C

Hey @Kelly Moore

That worked! I was so close! Thanks very much for the assist!