Free Search in Data List

[beatdrfranck]

Currently I am preparing a new file, where I would like to add some information for an item (like item number etc.) out of a master sheet. T

herefore in the "Data Validation" I added the list of item names.

When I enter a value in the cell where the data validation is active it searches for the word (e.g. "hammer") and find several entries, but only the ones where the word start with "hammer" or where the "hammer" comes after a space.

But it doesn´t find the word Schlosserhammer, where the word "hammer" is part of the word. So I have to type in "schlosserhammer".

Is there any way to make the data validation search in the full word instead of just at the beginning or after a space?

Thanks in advance and best regards, Frank

 

[Georgiboy]

Is the file a '.xlsm' file?
Does it contain any code?

 

[beatdrfranck]

currently not. Its just a "normal" excel file

 

[Georgiboy]

I see, I asked as when I use data validation with a list it does not narrow the results down.

I was wondering if you have any formula/ code or filters that are doing this in the background.

 

[Fluff]

when I use data validation with a list it does not narrow the results down.

Is that with 365 as it's now searchable to an extent.

Is there any way to make the data validation search in the full word instead of just at the beginning or after a space?

AFAIK that can only be done with VBA & a combobox, rather than normal data validation.

 

[beatdrfranck]

okay. Thanks so far!

 

[Georgiboy]

Is that with 365 as it's now searchable to an extent.

Good point, no, I don't have my laptop today so I tested it in Excel 2016. Also I did not know that Excel 365 did that - you learn something new everyday.

 

[beatdrfranck]

This is one of the best new things in 365

 

[JEC]

You can do it with one line of VBA/combobox.

Also possible without VBA with a little workaround using a textbox placed over a cell(and linked to this specific cell)

 

[beatdrfranck]

Can you send me an excel example?