0

I have a google form that populates a row with answers including User and Activity. I have 75 users and 63 activities to check. On a second tab I have a column of users and a header row of activities. I would like this second tab to populate a cell green with "0" if it finds both the user and the activity on the form. I would like it top populate red with a "1" if it is not complete. This allows for a rapid check on what activities the user has completed and provide a way for me to also total up the number of activities remaining for any user or any activity as well.

The user is in column C on the form response and the activity is in column D.

[enter image description here]enter image description here

1

1 Answer 1

0
=BYROW(TOCOL(A2:A,1), LAMBDA(u, 
   BYCOL(TOROW(B1:1,1), LAMBDA(a,
   --NOT(COUNTA(IFERROR(
     FILTER(form!C:C,(form!C:C=u)*(form!D:D=a)))))))))

Calculation Sheet

Form Results Sheet

Conditional Format Rules

Detailed Rule

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.