• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Searchable drop down list

DIlani

New Member
Hi

I need to enter paddy variety in the column "Paddy Variety" under the Detail Bill sheet. I need to have multiple drop down lists(Around 5000) in column "Paddy Variety" with searchable option.
I have managed to set up a searchable drop down list in the first row WHICH WORKS FINE, but when I copy the drop down list to the next row. it is no longer searchable and I can only get a complete list of all paddy varieties in my drop down list. I have used the following formulas:

OFFSET
IFERROR(VLOOKUP(ROWS .....
IF(ISNUMBER(SEARCH ....... MAX
together with data validation to set up the searchable drop down list.

I need to do this with formulas without combo Boxes.
Could somebody please help on this
 

Attachments

  • Dilani Paddy20180101-Jan.xls
    98.5 KB · Views: 6
Last edited:
Try,

1] In "Dropdown List" sheet, Col C "Unique List" C2, formula copied down :

=IFERROR(INDEX(B$2:B$100,AGGREGATE(15,6,ROW(B$2:B$100)-ROW(B$1)/ISNA(MATCH(B$2:B$100,'Detail Sheet'!D$2:D$100,0))/(B$2:B$100<>""),ROWS($1:1))),"")

2] In "Detail sheet", Col D "Paddy Variety", select D2 >> Data Validation >>

>> Allow : List

>> Source :

=OFFSET('Dropdown List'!$C$1,1,,COUNTIF('Dropdown List'!$C:$C,"?*")-1)

then, copied down.

Regards
Bosco
 

Attachments

  • Dilani Paddy20180101-Jan(1).xls
    101.5 KB · Views: 21
Back
Top