# Question & Solution - Searchable Drop Down List

• I came across a method to search through drop down list as below -

1) Paste the data in a Column (Column A)
2) Hide the Rows (60 Rows)
3) On cell 61 click - Ctrl + F3 , click new , give a name (test)
4) In the Refers to tab use formula - =OFFSET(Sheet1!\$A\$1,0,0,MATCH("*",Sheet1!\$A\$1:\$A\$60,-1),1)
5) On cell 61 again do Data Validation , choose list , in source tab , click F3 and choose - test
6) Type "a" in cell 61 and click the drop down button and it gives all the data starting with letters "a"

Now, I have done the same search through drop down list as below -

1) Paste the data in a Column (Column A)
2) Hide the Rows (60 Rows)
3) On cell 61 type "a" and click - Alt + Down Arrow - and it gives all the data starting with letters "a"

Whats the difference between both these methods as I get the same result using both methods.
Would also like to know what the Formula means.

Have attached sample sheet, On Sheet 1, I have used Second method and on Sheet 2 I have used First method

PS - To view the data just do Ctrl+A, Right Click and select Unhide

Also, is there a Better way to do a searchable drop down list, not interested in macros.

## Files

• Re: Question &amp; Solution - Searchable Drop Down List

One major difference, is that one would need to know (and remember) that they need to use the ALT + Down Arrow to activate the list in your second method.

The formula: =OFFSET(Sheet1!\$A\$1,0,0,MATCH("*",Sheet1!\$A\$1:\$A\$60,-1),1) creates the range of values for your named range, Test. MATCH("*",Sheet1!\$A\$1:\$A\$60,-1) finds the last row containing a string in range above your drop down. So the list is created by Offsetting from A1 by 0 rows and 0 columns, and a height of MATCH("*",Sheet1!\$A\$1:\$A\$60,-1) result (e.g. 60 rows), and width of 1 cell.

Not sure of any other non-macro methods.... just variations of your first (ie. different OFFSET formula, or foregoing the Named Range and putting the formula directly in the Data Validation source field).

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Question &amp; Solution - Searchable Drop Down List

Thanks

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!