Searchable Dropdown List

[Bedford]

Using a formula for a searchable drop down list that I picked up online;

=SORT(FILTER(A14:A1983,ISNUMBER(SEARCH('CONSUMER DATA'!Y5,A14:A1983)),"not found"))

It works well in the list in cell; 'CONSUMER DATA'!Y5, however, I’m needing to expand the formula to include Y5 to Y1983. I’ve tried increasing the range;

=SORT(FILTER(A14:A1983,ISNUMBER(SEARCH('CONSUMER DATA'!Y5:Y1983,A14:A1983)),"not found"))

But it returns a #VALUE error.

Any suggestions would be appreciated.

 

[Domenic]

Try . . .

=LET(Data,A14:A1983,Criteria,'Consumer Data'!Y5:Y1983,SORT(FILTER(Data,IF(MMULT(IF(LEN(TRANSPOSE(Criteria))>0,ISNUMBER(SEARCH(TRANSPOSE(Criteria),Data)))+0,ROW(Criteria)^0)>0,1,0),"not found")))

Hope this helps!

 

[Bedford]

Try . . .

=LET(Data,A14:A1983,Criteria,'Consumer Data'!Y5:Y1983,SORT(FILTER(Data,IF(MMULT(IF(LEN(TRANSPOSE(Criteria))>0,ISNUMBER(SEARCH(TRANSPOSE(Criteria),Data)))+0,ROW(Criteria)^0)>0,1,0),"not found")))

Hope this helps!

I think this can work, my other dilemma is with the formula in the data validation in the drop down list. I put the formula as; ='PRODUCT CODES'!$A$14#, but this returns an error and won't allow for searchable features?

 

[Domenic]

I tested it on my version of Excel, which is Microsoft 365 for Windows, and it seems to work fine.

 

[Bedford]

I tested it on my version of Excel, which is Microsoft 365 for Windows, and it seems to work fine.

I think it's more complicated than I originally posted. Your formula works fine in the sheet where the data is kept, Sheet 1 for example, in Sheet 2 I have a data validation list with a formula; ='Sheet2'!$A14# that is supposed to communicate with the cells in Sheet 1 to act as a searchable dropdown list, but the data validation is resulting in an error. Confused?

 

[Domenic]

If Sheet1 contains the result from the dynamic array formula, shouldn't your data validation be referencing Sheet1 instead of Sheet2? So shouldn't it be . . .

='Sheet1'!$A14#

? ? ?

 

[Bedford]

If Sheet1 contains the result from the dynamic array formula, shouldn't your data validation be referencing Sheet1 instead of Sheet2? So shouldn't it be . . .

='Sheet1'!$A14#

? ? ?

Likely I'm not explaining it correctly. So in Sheet 1, I have data, product codes, and corresponding pricing, description etc. I've used your formula in Sheet 1 in the column adjacent to the column containing the product codes. Then in Sheet 2 I've added the formula; ='Sheet 1'!$A14#, and when I start to make an entry in Sheet 2, I'm hoping to have a list showing items with similar characters appear so I can choose from the list, the correct entry I'm needing. If I type the first 2-3 characters no list appears, if I press return/enter, the column with the formula you provided shows only those product codes beginning with the same 2-3 characters entered in Sheet 2, and back in Sheet 1, now that return/enter has been hit, the available items in that cell only, has the appropriate product codes matching the 2-3 characters entered in Sheet 2.
So it does work, in the sense that after pressing return/enter the selection is narrowed down, I'm simply hoping I can start typing and as I do so, I'm offered a list of closely related product codes.
My dilemma in a nut shell.

 

[Domenic]

Oh I see. Unfortunately, I won't be able to provide you with a solution for your Mac version of Excel.

Maybe someone else here on the forum will be able to provide you with one.

However, since your original question only asks for help with a formula, I would suggest starting a new thread and asking your question there.

 

[Domenic]

I just took a quick look and found this . . .

AutoComplete in Data Validation list | Mac Excel Automation

AutoComplete in Data Validation list in Mac Excel

macexcel.com

Hopefully it helps!

 

[Bedford]

I just took a quick look and found this . . .

AutoComplete in Data Validation list | Mac Excel Automation

AutoComplete in Data Validation list in Mac Excel

macexcel.com

Hopefully it helps!

I will give it a try, thank you.