Smart Search in Excel

[deepakhp]

Hi,

I am trying to get excel to work like a webapp(eg: google search) where we can have smart search.
Problem Statement : I have a list of things in Sheet 1 (Eg: over 100 unique entries in a column, sheet 1 is like a database), which the user is currently using via a drop-down (scroll down and choose).
Require : Smart Search method so that use can for example input "A" where all entries/texts starting with "A" would be populated and make it easier for user to choose. This can also be in drop downs where use can input and drop down get updated based on input.

I am hoping we can do this in excel. If not, do i need to use some database tool for storing my database and then doing this via some scripting language?
Any help here would be appreciated.

-Deepak

 

[AD_Taylor]

I have actually created something like this before that should work with in cell Data Validation drop downs and also the Combo box form control. I can try and get a sample together for you if you like and upload it via Skydrive?

Let me know which style of drop down and also what version of Excel you are using and which operating system you are using?

 

[erik.van.geit]

Hi,

See Jaafar Tribaks autowordcompleter
http://www.mrexcel.com/forum/excel-...-wordcompleter-you-type-cell-now-working.html

kind regards,
Erik

 

[AD_Taylor]

Hi Erik,

Looks great to be honest and probably what the OP wanted.

Just wanted to mention that my solution was formula based so avoids the need for VBA which some companies tend to dislike (the need to enable macros and the security reasons are mainly stated).

Also I can't get Jaafars code working on Excel 2010 on Windows 7. As I'm running a 64 bit system the code asks me to make the declarations PtrSafe. I know this is normally just a case of making Long variables LongPtr for example but I don't want to break the code by putting something in the wrong place!
So just a note that while it should work on 32 bit systems, 64 bit may struggle.

 

[deepakhp]

thank you Erik, i shall look inot the Auto Word complete, on initial view of this post, seems this may not work in Excel 2010 on win 7, however i shall recheck the same

 

[deepakhp]

thank you for the reply AD_Taylor...appreciate any helpo i can get
I have combo box control for drop down, using Excel 2010 and Win 7 (same as your config ) and yeah it seems that the Jaffers Auto Complete may not work for this config

 

[AD_Taylor]

I've uploaded my example to my SkyDrive now.

If you go to this address, http://sdrv.ms/Wgz8Nq, go into Test Documents (or Template Documents can't remember if I renamed it) and right click the file called 'Filterable Drop Down Lists (Based on text value)'.
You'll want to select the option to Download. If you had just single clicked the file it would have tried to open in Excel Web App which probably won't let you see all the features.

If you have any questions feel free to get in touch

 

[erik.van.geit]

thank you Erik, i shall look inot the Auto Word complete, on initial view of this post, seems this may not work in Excel 2010 on win 7, however i shall recheck the same

Indeed, just tried me too. And Jaafars private box is full, so can't send anything to ask.
But here is another one
BET: Microsoft Excel Data Validation - Drop-Down List - AutoComplete

 

[erik.van.geit]

Hi Erik,

Looks great to be honest and probably what the OP wanted.

Just wanted to mention that my solution was formula based so avoids the need for VBA which some companies tend to dislike (the need to enable macros and the security reasons are mainly stated).

I agree. Of course if they don't want macros, then there are some limitations sometimes.
Anyway, it's a nice challenge to do it using formulas.

 

[unity]

I have actually created something like this before that should work with in cell Data Validation drop downs and also the Combo box form control. I can try and get a sample together for you if you like and upload it via Skydrive?

Let me know which style of drop down and also what version of Excel you are using and which operating system you are using?

Dear AD_Taylor,

The Skydrive folder that the link points to is emply. Would you be kind to post your example once more. I would really appreciate that. I looking for a solution that creates a filterable drop down list based on text value in Excel 7.
Thank you