Searchable Data Validation List

[jdellasala]

I started with a Data Validation Drop Down List in cell N1 that used the column "Symbol" which is the first column in a table named "StockData" in a worksheet named "Stock Data". The column is a Stock data type. It worked great, but there were 81 Stock Issues listed, and I wanted to be able to type a few characters into cell N1 such that only a list of the Stocks that had those letters would appear - so typing "ap" would list only the three Stocks with "ap" in the name/symbol such as "APPLE INC. (XNAS:AAPL)". Somehow I actually got this to work, however now I can't reproduce it!
The Data Validation List now points to:

='Industry PTs'!$O$4#

That cell has the formula:

=FILTER(StockData[Symbol],ISNUMBER(SEARCH(Dashboard!N1,StockData[Name '[Symbol']])),StockData[Symbol])

This actually works in the original worksheet, but as you can see I have no idea where they syntax for the within text option

StockData[Name '[Symbol']]

comes from, certainly doesn't look right to me, and no matter how much I try to reproduce it using a similar table worksheet names, etc., I've been unable to reproduce it.

The beauty of how this works in the original worksheet is that since the returned value is a Stock Data Type, you can click on the icon to the left of the selected item and see the item's "card" which displays (among other things) a description of what the company does, AND you can reference the cell (N1) in other cells in the Dashboard (where it is) to get other data on the stock like =$N$1.Price to get the current price (absolute reference isn't needed, but I was glad I used it so that when I wanted to change an item's location the formula still worked).

I've uploaded two images. As you can see the formulas that reference the (resulting) Rich Data type (N1) have a value of #FIELD before selecting an item from the drop down list since the typed value "ap" is not a Rich Data Type, however once selected all the formulas work.

Thanks for your feedback on this.