How do you do a 'search as you type' in excel?

[RichardJSigKits]

Hi Guys.

I have a form in Excel, which needs to display a row of data by using a search as you type field, where the results are shown that include any part of what you type.
EG. If a user wants to search for an Appliance that has a product code of LFT32147HX, and the user can't remember the whole code and types '32147...' into a cell, excel returns the results of all appliances with that text within its product code.
If it is not possible to do it with text from the middle of the code, is it possible to do it using the first few characters, eg. 'LFT321...' and for it to return all available options.
Attached is an example of what I need. The top part is the part that will be visible by the user, and Ideally the Appliance Catalogue data should be on a completely separate sheet.
As you can see from the attached, if you type LFT32147H in A4 under 'Model' and then press enter, you get an error because it needs the exact code as it is in the data in order to return it as a result. Simply add the X to the end like 'LFT32147HX', then it returns the result. Trouble is, a lot of the appliances have extra letters on the end of the code that many users will not know, so it needs to return results as you type them, ideally so you can click the one you want and it add to the form, or at least so you can then see the correct code to type in.

Does this make sense?

[Kyle123]

Have a play with this and see how far off it is

[RichardJSigKits]

Wow, man, this is seriously great work! Thank you so much for your time!

Whilst I am generally 'Okay' with excel, using IF statements and all that, I find this type of thing fascinating - I have to admit, I don't really understand the language of VBA and all the visual basic stuff, but once I know what needs to go in there, I know how to put it in....

Not quite how I envisaged this to work, but work it does.

Whilst I bow down to your genius, there are a couple of things I am a tad concerned about:
- This will prevent me from locking cells and protecting the workbook (To prevent users from changing what they shouldn't) as it will hit a run time error '1004' "Cannot use the command with Protected Sheet"
- How I will integrate this into my single quote form that also needs to do the same for Sinks,taps,work-surfaces and other products as well. Maybe I will need to add all products to one MASSIVE data sheet?

I was hoping to be able to understand how to do this, then be able to integrate it as I need it across the rest of the product range as well as the appliances.

My Big plan is to have a one page form that can do it all to create a quote... At the moment, I have a confusing workbook with loads of tabs across the bottom, and the user has to navigate their way backwards and forwards through the different sheets to create a quote, and it's currently taking far too long to do just one quote. I need to get to a stage where my users can open a one page form, and literally type in what they want with all the pricing to to be attached - all they should do is enter the product codes, and the quantity - and that's it... might be taking on more than I can chew with this, but I'll get there.... one decade or the next...

Have a play with this and see how far off it is

[Kyle123]

I'd just have a huge list I reckon that would be easiest - I've just run a test with 500k items in the datasheet and it's still nippy

It's actually pretty simple what's going on here, all the code does is autofilter the data just like you would manually, then copy and paste it into the search tab.

You can lock cells all you like, they just need unlocking whilst the code runs, you can also hide the data tab altogether - have a look at the updated version. The issue with your layout before is that you could have a lot of matches in your search term and so you need somewhere to display them all.

[RichardJSigKits]

Thanks again for your help, you're an absolute diamond.

Huge list it is, then.

With regard to the locking of the cells, it surprises me that you cannot do this, as surely that's the point of having the ability lock the entire sheet and leave certain cells 'Useable'?.. it would cause a major problem if this can't be done as I simply cannot trust other people to not change what should not be changed - for example the price! People have a nasty habit of changing something to suit their own needs, and I really need to protect this from happening.
Hmm... I'll have to have a think about that...

Although I think this may well be the route I need to go down, can you think of any way to do a similar search that will enable locking at all?

Thanks once again for your valuable time and effort

Kind Regards

I'd just have a huge list I reckon that would be easiest - I've just run a test with 500k items in the datasheet and it's still nippy

It's actually pretty simple what's going on here, all the code does is autofilter the data just like you would manually, then copy and paste it into the search tab.

You can lock cells all you like, they just need unlocking whilst the code runs, you can also hide the data tab altogether - have a look at the updated version. The issue with your layout before is that you could have a lot of matches in your search term and so you need somewhere to display them all.

[RichardJSigKits]

I've been playing with this, and I got confused because I unprotected it, had a play about with it, then found I have to unprotect it again, as it keeps protecting itself
Looking at the code, I see you have put it in to do so.... Very clever...

Thanks again for your help, you're an absolute diamond.

Huge list it is, then.

With regard to the locking of the cells, it surprises me that you cannot do this, as surely that's the point of having the ability lock the entire sheet and leave certain cells 'Useable'?.. it would cause a major problem if this can't be done as I simply cannot trust other people to not change what should not be changed - for example the price! People have a nasty habit of changing something to suit their own needs, and I really need to protect this from happening.
Hmm... I'll have to have a think about that...

Although I think this may well be the route I need to go down, can you think of any way to do a similar search that will enable locking at all?

Thanks once again for your valuable time and effort

Kind Regards

[Kyle123]

Glad you got it sorted