Forum Discussion
sf49ers19238597
Jan 20, 2025Iron Contributor
I need help with three difference searchable dropdown lists
I need help with three searchable dropdown lists on a team info worksheet. I want to make the work searchable with no repetition. The list goes on the worksheets. When a team is picked, the team will not show on the list also no blanke in the dropdown to
Bye weeks worksheet
Range("B6:B11,E6:E11,H6:H11,K6:K11,N6:N11,Q6:Q11,B14:B19,E14:E19,H14:H19,K14:K19,N14:N19,Q14:Q19,B22:B27,E22:E27,H22:H27,K22:K27,N22:N27,Q22:Q27")
NFL playoffs worksheet
AFC PLAYOFFS TEAMS SECTION
Range("B6,B8,B10,B12,B14,B16,G8”)
NFC PLAYOFFS TEAMS SECTION
Range(“B20,B22,B24,B26,B28,B30,G22”)
Thank You
- sf49ers19238597Iron Contributor
I need help fix my searchable dropdown instead
- m_tarlerBronze Contributor
You can use a temporary column to hold the available options using a formula like:
=FILTER(Data[info],NOT(ISNUMBER(XMATCH(Data[info],J9:M9))),"")
where "Data[info]" is the list of all options and the range "J9:M9" is the range where you are entering the data, as shown is the image below. As each of those cells J9:M9 has Data Validation = a List defined as $O$9# in this case which points to this temporary list being created:
- sf49ers19238597Iron Contributor
Did took pictures and file
- m_tarlerBronze Contributor
in attached file the 'BYE WEEKS' tab already has Data Validation (drop downs) set to the 'TEAMS REMAINING' column on the 'TEAMS_INFOS' tab (D37). I updated the formula in cell D37 to be
=FILTER(TEAMS_NAMES,NOT(ISNUMBER(XMATCH(TEAMS_NAMES,TOCOL('BYE WEEKS'!B3:Q25)))),"")
this formula will dynamically update that list to be only the teams not picked yet.
Delete names on the BYE WEEKS tab and you will see.
NOTE: If you try to attach a file for us it appears you can NOT attach a .XLSB file type. I may be possible to save as a macro-enabled file (.xlsm) but better yet, especially if you aren't asking for help with a macro, please save as .XLSX