Using data validation you can create useful drop-down lists in Excel.  If your list contains lots of values, it might be worth making it searchable.  The video below explains how this can be done – no VBA required!  Download the featured file here.

Formulas used in the video

Workings column
=IF(ISNUMBER(SEARCH(‘Drop Down List’!$A$2,[@Product])),MAX($B$1:B1)+1,0)

Drop down list values
=IFNA(INDEX(ProductList[Product],MATCH(ROW(D1),ProductList[Workings],0)),””)

Named Reference Formula
=OFFSET(‘Product List’!$D$2,,,MAX(ProductList[Workings]))

Posted by Chester Tugwell