I am building an APP for an annual junior (participants under 21) competition. There is a list of eligible entries tagged with unique number identifiers. Using this APP, the public will be able to vote and each voting person will submit once after they pick their ‘Top 5’ from 1st to 5th from a dropdown list of all eligible entries. Then the APP/Google Sheets will calculate the results and determine how the entries are placed from 1st to 5th based on the highest rankings for each placing from the aggregated placings submitted from each voter.
The issue I am having is that I have an APP tab for voting in which the layout is there is a dropdown of all of the unique number identifiers available for each placing, for which, a voter will make one selection from this same list for each placing from 1st through to 5th.
Voter APP layout Example:
1st Place > Dropdown List of eligible entry numbers
2nd Place > Dropdown List of eligible entry numbers less the number selected for 1st Place
3rd Place > Dropdown List of eligible entry numbers less the number selected for 1st & 2nd Place
4th Place > Dropdown List of eligible entry numbers less the number selected for 1st, 2nd & 3rd Place
5th Place > Dropdown List of eligible entry numbers less the number selected for 1st, 2nd, 3rd, & 4th Place
What I need to do is when a voter makes a selection for their 1st Place choice, then moves to selecting their 2nd Place choice, I want to remove the number that was selected for 1st Place, from the list of available choices for 2nd Place, and so on down to 5th Place, so that the same entry cannot be selected more than once from the dropdown list for each placing.
The APP will only submit each voter’s selections once after they have made all of their selections from 1st through to 5th. This Expression will have to run in the APP, but, I have no idea how to create this Expression for this purpose.
The APP has an ‘Admin’ Workbook with a Worksheet for recording all of the entry data (Entries) and a Worksheet for selecting status of the competition (Status) to control the time period for accepting entries and votes. A second Workbook with a Worksheet for collecting the votes (Votes) and a Worksheet for running placing calculations (Results).
I would sincerely appreciate your assistance. Thank you.