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...
excel
Formulas and Functions
m_tarler's avatar
m_tarler
Bronze Contributor
Jan 20, 2025

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:

 

  • sf49ers19238597's avatar
    sf49ers19238597
    Iron Contributor
    Jan 21, 2025

    Did took pictures and file

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Jan 21, 2025

      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 

      • sf49ers19238597's avatar
        sf49ers19238597
        Iron Contributor
        Jan 30, 2025

        I like it but I want when pick it will not show on the list again

Resources