SOLVED

Setting up Searchable Dropdown list (WITHOUT Filter function)

%3CLINGO-SUB%20id%3D%22lingo-sub-2154638%22%20slang%3D%22en-US%22%3ESetting%20up%20Searchable%20Dropdown%20list%20(WITHOUT%20Filter%20function)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2154638%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20set%20up%20a%20searchable%20dropdown%20list.%20It'll%20effectively%20be%20for%20searching%20for%20clients%20by%20name.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20avoid%20using%20the%20filter%20function%20as%20not%20everyone%20will%20access%20the%20files%20in%20365%2Fweb%20version.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.bluepecantraining.com%2Fcreate-a-searchable-drop-down-in-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.bluepecantraining.com%2Fcreate-a-searchable-drop-down-in-excel%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20used%20the%20formulas%20from%20the%20video%2Fworkbook%20linked%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20%3CEM%3Ealmost%20working.%26nbsp%3B%3C%2FEM%3EAs%20best%20I%20can%20tell%2C%20the%20issue%20lies%20in%20the%20%22Workings%22%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20formula%20from%20his%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(ISNUMBER(SEARCH(%E2%80%98Drop%20Down%20List%E2%80%99!%24A%242%2C%5B%40Product%5D))%2CMAX(%24B%241%3AB1)%2B1%2C0)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20spits%20out%20the%20occurrences%26nbsp%3Bof%20the%20letters%20typed%20in%20his%20form%20(so%20If%20I%20type%20d%2C%20it%20returns%201%20for%20the%20first%20d%2C%202%20for%20the%20second%2C%203%20for%20the%20third%2C%20etc)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhen%20I%20set%20up%20the%20same%20formula%20in%20my%20spreadsheet%2C%20it%20is%20only%20returning%201s.%20So%20it%20just%20counts%201%20everytime%20the%20letter%20appears%2C%20not%201%2C2%2C3%20etc.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThat%20seems%20to%20break%20the%20rest%20of%20the%20formulas.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAny%20help%20greatly%20appreciated.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2154638%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2154703%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20up%20Searchable%20Dropdown%20list%20(WITHOUT%20Filter%20function)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2154703%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949996%22%20target%3D%22_blank%22%3E%40Davidm54%3C%2FA%3E%26nbsp%3BWell%2C%20for%20the%20sake%20of%20folks%20searching%20this.%20The%20formulas%20in%20the%20link%20work.%20The%20answer%20is%20to%20not%20mess%20up%20cell%20references.%20I%20had%20one%20column%20doubling%20up.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELink%20in%20OP%20is%20worth%20saving%20if%20you%20want%20to%20do%20the%20searchable%20drop%20thing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi All,

 

I'm trying to set up a searchable dropdown list. It'll effectively be for searching for clients by name. 

 

I am trying to avoid using the filter function as not everyone will access the files in 365/web version.

 

https://www.bluepecantraining.com/create-a-searchable-drop-down-in-excel/

 

I've used the formulas from the video/workbook linked above.

 

It's almost working. As best I can tell, the issue lies in the "Workings" column.

 

Here's the formula from his sheet. 

 

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

 

This spits out the occurrences of the letters typed in his form (so If I type d, it returns 1 for the first d, 2 for the second, 3 for the third, etc)

 

When I set up the same formula in my spreadsheet, it is only returning 1s. So it just counts 1 everytime the letter appears, not 1,2,3 etc.

 

That seems to break the rest of the formulas.

 

Any help greatly appreciated.

1 Reply
best response confirmed by Davidm54 (Contributor)
Solution

@Davidm54 Well, for the sake of folks searching this. The formulas in the link work. The answer is to not mess up cell references. I had one column doubling up. 

 

Link in OP is worth saving if you want to do the searchable drop thing.