Excel Searchable Drop-Down Menus and More Drop-Down Fun

One of the most used, but most under-utilized tools in excel would be data validation lists. G sheets has solved one of the issues I am going over by automatically allowing users to search a drop-down list by simply starting to type in a given word, however if you wanted to do this in excel, it is not as easy as checking a box.





Pricing
*After payment is received, you will be sent the spreadsheet via e-mail.

The template I have designed does two things. The first is a searchable-drop-down and the second is a conditional drop-down menu that populates based on a contingent value. I will explain further below.

So, first the searchable side of things. This is really helpful for users that have big 500-1000+ lists of unique sku's or items that have to be used within a drop-down data validation list because there is no room for error in picking the item name. Also, if you plan to build other logic based on the value you pick being accurate compared to everything else in the spreadsheet, then this is also going to be highly necessary.

It works in a simple way. You can start typing in the word you are searching for. Then, click the little arrow on the drop-down menu and it will show the items in your total list that match with the word you are typing in.

Ok, so simple enough.

The next step I took was then putting you in a real life scenario. What if you had 1,000's of products at all kinds of stores and you want to quickly find which stores have a given product that you pick. Instead of combing through the raw data to find what stores have a given unique product/item, let's do some formulas and find it quickly.

Well, that is where the next trick comes in. The second drop-down you will see in the video will allow you to put in a given value in one cell (such as a unique product or item) and then the drop-down list you get when clicking on the menu arrow will only be of the stores that contain that item.

Based on your raw data sources, the logic is setup to be dynamic to whatever value you put in and based on that you will always and only get the stores that have the given unique item.

Finally, within that drop-down, there will be no spaces or gaps and all the stores that populate will always be at the top. This solves a problem I had earlier in the year with a project that needed such functionality. What I had was a data validation list that was of a column that had tons of blanks in-between all applicable stores. Now, there is a new column that populates with only the right stores and they start in row 1 down, making for a really clean drop-down list.

No comments:

Post a Comment