**Searchable Drop Down List on Separate Sheets**

Articles on: Excel

**Searchable Drop Down List on Separate Sheets**

Min 4 x column are required on source sheet




Cell A2                  A1 is empty cell and A2 formula is (Past and Drag to end)

=IF(ISNUMBER(SEARCH(INDIRECT(CELL("Address")),B2)),MAX($A$1:A1)+1,0)

Original List        Original List is only your text


Filter List             C2 formula is      =IFERROR(VLOOKUP(ROWS($B$2:B2),A2:B38,2,0),"")


Helping Column               Rename Sheets as formula, or rename formula as sheet. Suppose I am using sheet name as data, copy formula, past and drag.          =OFFSET(data!$C$2,,,COUNTIF(data!$C$2:C38,"?"))


 Define Name     Copy Serial 5 formula, Open Define Name from formula tab.


Name it, Remove Refer to, Past your copied formula here, Ok



Drop Down List                 Now go to next sheet , select desire range of List, after coloring and bordering go to Data tab, Data Validation, List, Click on source and Press F3, Choose your name which allotted in Step 6.


Again go to data, data validation, remove error check mark and save the work and enjoy search able drop down list by pressing a name and press enter, now check drop down.

Updated on: 31/01/2023

Updated on: 01/07/2024