If you have a sample file to upload, this would greatly aid in developing a course of action.

Locating a specific item in an unsorted list can be both tedious and time consuming, especially when the list contains hundreds or thousands of items.

With no items to search for, the complete list is displayed.

I have a similar solution using VBA that is covered in my VBA course using User Forms.

This was most likely the exclusive domain of the ultra-advanced Excel user.

The issue is that Data Validation dropdown lists do not work with named ranges that contain Dynamic Array references.

This will keep us from having to switch back-and-forth between sheets during the development of the solution.

In this case, we wish to locate the text in cell D1 within every item of the Customer list.

Since the formula is returning TrueFalse response, any item with a True response will be included in the resultant list, while any item with a False response will be filtered from the list.

Because we want to capture the entire spilled array that begins in cell D2, add a pound (or hashtag) symbol to the end of the reference.

If the name is not found, return empty text (two double quotes).

You will get the new functions once its rolled out in a future update.

Remove any values in the cells below and your formula will work.

Unfortunately its not easy to set up in Legacy Excel you need to use names in name manager and few more steps.

To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question.

No matter your issue, Im certain someone there can inform you of the best way to reach your solution.

I hope you find success with this fantastic group of Excel enthusiasts.

My object was to get a dynamic dependent list of policies when I choose names from the List of Policy Holders.

I have used for dependent list data validation condition the formula: OFFSET(INDIRECT(SUBSTITUTE(D3,,)),0,0,COUNTIF(INDIRECT(SUBSTITUTE(D3,,)),),1) where D3 is the selected Name from the parent Policy Holder drop down list.

So, every time I have to add a new Policy Holder by expading the Table, I have to go to Define Name and add the new name with underscore replacing spaces.

Can you suggest a better approach.

B. I want to display the first available Policy number under a chosen Policy Holder immediately before the dropdown Dependent List is displayed.

Excel For Mac 2011 Drop Down List