基于先前选择的菜单项动态更新菜单

Question

So I'm thinking of making an NFL Team Pick 'em Google Sheet for my co-workers and I to use this upcoming NFL season. Every week, we will pick an NFL team to win a game and we can only pick each team once. 1 point for every correctly picked game. Most points wins the pot.

I need some help with how to pick the team each week so that you can only pick a team once. I'm not sure if this is possible in a Google Sheet. If so, how can I have someone pick a team from all 32 NFL teams the first week, then the menu for week two automatically excludes the pick from week 1. Then week 3 automatically excludes the pick from both weeks 1 and 2 and so on.

Or would a project like this be better suited in PHP/Java/HTML? If so, how would I handle the same menu principles in PHP?

Thanks in advance for any help!

Answer 1

Okay I figured it out.

I have one column that lists all the nfl teams K4:K35.

I have a data validation cell A4 that looks at the nfl teams on K4:K35.

In cell A24 I put the formula "=filter(K4:K35,K4:K35<>A4)" that filters the nfl team list K4:K35 except for the week 1 pick in cell A4.

I then have a data validation cell B4 that looks at the A22:A55 list for users to pick for week 2.

In cell A24 I put the formula "=filter(K4:K35,(K4:K35<>B4)*(K4:K35<>A4))" that filters the nfl team list K4:K35 except for the week 1 pick in cell A4 week 2 pick of B4.

Lather rinse repeat, and hide some columns and rows. One sheet for each participant. I'll add point totals with a master point total sheet too later on.

Jerome