I'm currently working on a google sheets file to organize the members of my class. I am currently assigning committees and I want them to choose their committee in Google Sheets. However, I want to apply only a certain limit per committee.

What I want to happen is, if a certain choice has been chosen i.e. 5 times, I would like that choice to disappear from the choices and would make it reappear again if ever a students change their choice, however, I do not know how to do this in terms of a formula or through data validation.

I would really appreciate your help. Thank you!

CodePudding user response：

Here's a toy example you may be able to adapt to your needs:

1. Create a list of options a,b,c,d,e in A1:E1 of Sheet1
2. Create a list of people Person1,Person2,Person3 in G2:G4
3. Apply data validation to H2:H4:

• Use criteria 'drop down (from a range)'
• Set the data range to =Sheet1!$A2:$E2 (only lock columns, not rows)

4. In A2 enter the following formula:

=lambda(people,choices,list,limit,
makearray(counta(people),counta(list),lambda(r,c,
if(index(choices,r)<>index(list,,c),if(countif(choices,index(list,,c))<limit,index(list,,c),),index(list,,c)))))(
$G$2:$G$4,$H$2:$H$4,$A$1:$E$1,2)

We are using MAKEARRAY to create a 2D array with the list of options on each line, however we are asking it to omit elements of the list from each line if they haven't already been selected AND a preset limit on the number of selections has been reached (in this example the limit is 2). Obviously in a 'real' example you would place the data range for validation in a separate sheet and probably hide and protect that sheet as well. You could also potentially use an array literal of strings rather than a cell range as the list of options in order to make the validation list formula completely self-contained.