There are 75 students split in 2 groups (36 and 39 students). There are 2 workshops with a max of 30 participants (mainly, group 1 goes to workshop 1 and group 2 goes to workshop 2). This is because most of the times, not all of the 75 students are coming to the workshops, so we don't always need to have 3 workshops (25 participants/workshop). However, in certain circumstances, some students can opt for changing the workshop that they want to participate in.
So, from the students POV: They submit the form:
- (Section 1 - Name and group.
- Section 2 (for group 1) - "Are you participating in Ws1?" - "Yes"/"No"/"Change".
- Section 3 (for group 2) - the same as section 1, but with Ws2.)
Inside the Sheet i have made 3 tabs:
- Form Responses 1,
- Workshop 1 (In A1 i have
=query('Form Responses 1'!$A:F,"Select * Where C='Group 1'"), and - Workshop 2 (In A1
=query('Form Responses 1'!$A:F,"Select * Where C='Group 2'").
Please help me with a formula or a script so that when (i.e.) student 3 from group 1 selects "Change", he can participate in workshop 2 ONLY IF student 5 from group 2 selects "No" (so Student 3 can take his seat) OR student 6 from group 2 selects "Change" (so they can switch places) automatically, based on those conditions). I need these conditions so that students from a group don't occupy the other's workshop seats unless a student originally assigned to that workshop declares his seat free.
I have tried this formula and it doesn't work: =ARRAYFORMULA(IF(D:D="Yes", query(A:E)"Yes", "") + IF(('Workshop 2'!E:E="Change") * (D:D="No") + ('Workshop 2'!E:E="Change") * (D:D="Change"), query('Workshop 2'!(A:E)), "")).
Also, please help me with formLimiter: I want to change the availability of form answers, based on # of participants submitted to a workshop. (i.e. if Group 1 answers of "Yes" + Group 2 answers of "Change" = 30, then in forms, Group 1 can no longer answer "Yes" and group 2 can no longer answer "Change". And vice-versa for Workshop 2. Also, if both workshops reach 30 participants, i want another option to appear in the "your group" question: "Group 3", which, if chosen, automatically sends to another section with Yes/No/Change for workshop 3.
Here is the link to Forms and Sheets folder (with editor access): https://drive.google.com/drive/folders/1lgter_-dBwWdBjc7OCCDGruCHSmfJhTC?usp=sharing