Forum Discussion

sf49ers19238597's avatar
sf49ers19238597
Iron Contributor
Jan 20, 2025

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

 

 

 

  • m_tarler's avatar
    m_tarler
    Bronze 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:

     

      • m_tarler's avatar
        m_tarler
        Bronze 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 

Resources