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.