Forum Discussion
Solved
Dynamic Data Validation Source
I'm finding this difficult to put into words without extensive explanation, so apologies in advance for what may be superfluous information.
Ten years ago, I created a spreadsheet for my family and me to track our NFL football picks against one another. The spreadsheet works very well for our purposes, but I've been wanting to streamline a certain aspect of it for a long time.
I'm going to assume some readers here are aware of the NFL and how it operates to avoid making this post too long:
My spreadsheet has a different page for every week of NFL play (Week 1, Week 2, ..., Postseason, etc).
I also have a page that lists all 32 NFL teams so that when I'm filling out each week's match-ups, I can use a Data Validation list (saving me from typing things out manually or accidentally breaking formulas via typos).
What slows me down filling out the schedule is every time I open a drop-down to select a team for the match-up, all 32 teams are shown. What I'd really like is for the Data Validation to shrink in size, removing all of the teams I've already chosen for that particular week.
Is there a way to instruct the Data Validation list to say, "hey, this is the source," but also to "please remove from the drop-down any selections already listed in 'these' cells here"?
If anyone needs screenshots or additional information, please let me know.
This requires Excel 365 in order to leverage the new "Dynamic Array" formulas.
I imported the team list in Screenshot 1 from the web. Column 1 of that imported table is the starting point for the validation list.
The formula for the data validation list is:
=SORT(FILTER(Standings___Detailed_View[NFL Team.1],NOT(ISNUMBER(MATCH(Standings___Detailed_View[NFL Team.1],tbl_UserInput[Team],0)))),1,1)That formula references the table on the second "Input Form" tab called tbl_UserInput (shown below). Once the team name is copied to that table, it will no longer be visible in the validation list:
For the purpose of keeping this simple, the validation list is referencing against team names in a single table. If the intent is to have 1 data validation cell for each week's schedule, the dynamic validation list formula posted above will require either more complexity or need to be duplicated once per weekly tab. Here is the portion of the formula that would need to be revised to point to different tabs/tables. Specifically, tbl_UserInput[Team] would need to be revised depending on the tab.
NOT(ISNUMBER(MATCH(Standings___Detailed_View[NFL Team.1],tbl_UserInput[Team],0))))Lastly, the syntax for the source of the dropdown list follows. Note the "#" at the end of the cell reference means it is referencing the entire dynamic array, as opposed to cell X2. I also disabled the "show alert..." on the Error Alert tab.
Hope this helps, or at least gets you started.
Dexter
2 Replies
This requires Excel 365 in order to leverage the new "Dynamic Array" formulas.
I imported the team list in Screenshot 1 from the web. Column 1 of that imported table is the starting point for the validation list.
The formula for the data validation list is:
=SORT(FILTER(Standings___Detailed_View[NFL Team.1],NOT(ISNUMBER(MATCH(Standings___Detailed_View[NFL Team.1],tbl_UserInput[Team],0)))),1,1)That formula references the table on the second "Input Form" tab called tbl_UserInput (shown below). Once the team name is copied to that table, it will no longer be visible in the validation list:
For the purpose of keeping this simple, the validation list is referencing against team names in a single table. If the intent is to have 1 data validation cell for each week's schedule, the dynamic validation list formula posted above will require either more complexity or need to be duplicated once per weekly tab. Here is the portion of the formula that would need to be revised to point to different tabs/tables. Specifically, tbl_UserInput[Team] would need to be revised depending on the tab.
NOT(ISNUMBER(MATCH(Standings___Detailed_View[NFL Team.1],tbl_UserInput[Team],0))))Lastly, the syntax for the source of the dropdown list follows. Note the "#" at the end of the cell reference means it is referencing the entire dynamic array, as opposed to cell X2. I also disabled the "show alert..." on the Error Alert tab.
Hope this helps, or at least gets you started.
Dexter
Thank you very much. This is what I was looking for and quite a detailed response. Many thanks for taking the time to write it up.
