so recently my principal is having me create a database for a tardy system recently what i've done is set up a google form where the student id can be enter through a num pad and put into a google sheet where ive setup 2 sheets one where it shows the student name and how many tardies they have and then the other is the google form responses, each tardies cell in the other sheet has the formula: =COUNTIF('Form Responses'!B:B,"") which essentialy checks the number of times a certain student id pops up, by ferpa i am not legally allowed access to the student id's is there any possible way i can maked it when a person enters their student id it adds/creates a new formula for the tardies cells to check through the entire list without a duplication or error code?

i have tried the =COUNTIF('Form Responses'!B:B,"") formula but that would make where my principal would have to edit a thousand lines of formula

CodePudding user response：

Depending on the exact setup of your sheets, you can use a formula like this, guessing that the students' IDs are in A column:

=BYROW(A2:A,LAMBDA(each,IF(each="","",COUNTIF('Form Responses'!B:B,each))))

That would set a formula will drag all the column automatically. Or, instead of having a list of IDs you can set a QUERY that will find all the ID values in the responses and their count:

=QUERY('Form Responses'!B:B,"SELECT B,Count(B) where B is not null group by B")