Help needed with Advanced search in Dropdown Menus and Data Validation

Nighthawk_NL

New member
Joined
Feb 7, 2020
Messages
5
Reaction score
0
Points
1
Excel Version(s)
2019
I have been reading and watching a lot of possible solutions online lately, but unfortunately I can't figure it out. Perhaps it is not possible at all in Excel what I would like to do.


On the "Basic" tab Column C (## Supplier ##) I want to be able to type e.g. Europe in row C2 and then like to get an overview of all Suppliers that contain the word Europe and I can easily select the correct Supplier.

It would also be very handy that if I enter the letter A that Excel will only show the lines that start with an A and / or a certain word such as Europe and that Excel will only show the dropdown data that contains this word. I want to be able to extend the search function to all the cells below.

This also applies to Column E (## Label ##). For example: When I type Software in Excel, it shows everything that contains the word Software so that I can easily select the correct label.

Finally, in Column G (Genre) when I type in Mouse Excel gives an overview of all Genres in which the word Mouse appears and I can easily select the correct Genre.
I hope that what I want is actually possible in Excel and that someone could help me with this.

In any case, many thanks for your help.
 

Attachments

  • Dropdown Validatie Sheet.xlsx
    26.4 KB · Views: 14
See if this solution works for you. It adjusts to your situation.
 

Attachments

  • Nighthawk_NL-navic-10477.xlsx
    57.9 KB · Views: 10
Hi Navic, many thanks for your reaction. But my current Excel knowledge is not good enough and I am afraid it would take me hours to adjust this to my sheet.
Is it possible (if you have time) that you applies this to my sheet ? You would be the best and I would be very very grateful.

Best,

Nighthawk
 

Attachments

  • Dropdown Validatie Sheet.xlsx
    26.4 KB · Views: 8
I apologize. My fault.
Now, I did realize that you were looking for an opportunity for multiple cells.
My search suggestion was for only one cell.
Sorry
 
I have adapted to your requirements.
Look at an example. That's where the helper columns are.

Add VBA code to Sheet2 Module (save as *.xlsm format)
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
 

Attachments

  • Nighthawk_NL-navic-10477-2.xlsx
    54.7 KB · Views: 11
Hi Navic, your solution works like a charm. Many thanks for your solution.
 
Hi Navic, I am very sorry to bother you again but I was wondering if it is possible to extent the functionality even further. Please see the tab "Dropdown NEW".

As you can see a Supplier can have more than one label. Is it possible when I choose for example the Supplier “Zound Industries International AB” from the Dropdown list in the tab “Basis” (Column C) I only get to see the labels from this Supplier. Like “Coloud Headphone, Marshall Headphones and Urban Ears Headphones” in the Label Column E. So I can choose the correct one and the Label ID is automatically inserted in Column F.

Beware: When I choose the Supplier the Supplier ID must be inserted as well in Column D.

I hope this is possible.
 

Attachments

  • Example.xlsm
    148.8 KB · Views: 8

Attachments

  • Nighthawk_NL-navic-10477-3.xlsm
    91.3 KB · Views: 5
Dependent Drop-Down List based on quick search

I made an example of dependent drop-down lists using a final example from the Internet. It was in a hurry. If there are mistakes, I apologize!
 

Attachments

  • Nighthawk_NL-navic-10477-3.xlsx
    149 KB · Views: 3
@Nighthawk_NL
Tell me I know if my solution helped?
 
Hi Navic,

Many thanks for your solution. I will test your solution as soon as possible. At the moment I'm occupied with something else but I will let you know as soon as possible.
 
Back
Top