The enhancement we will make takes the idea of a single-row searchable drop-down list…
…and extends the feature down a column.
The Master List
The complete list of customers is located on a sheet named “MasterData”.
This is the list we want to filter and display as a drop-down list. The drop-down list should display an entry regardless of the character position of the searched text; beginning, middle, or end.
Our list of Customer and Company has been set up as an official Excel Table named “TableCustomer”.
The Original Solution
To refresh your memory, the original solution for returning a searchable drop-down list was built using the new Excel Dynamic Array functions that are available in Office 365.
The problem with the original solution is that it only worked in a single cell. We couldn’t repeat the formula down a column for additional use.
The reason it could not be repeated down a column is that the Dynamic Array formula created a filtered list on a supporting sheet.
If we were to repeat the formula to the cell directly below the current cell, the spill range of an above result would intersect with the spill range of the next lower formula, causing the formula to produce a #SPILL! error.
(imagine the below column results stacked atop one another in the same column)
There are several Dynamic Array functions used in the final formula, so we will quickly look at each function. Remember, a detailed explanation can be viewed in the previous post (link).
Creating the SEARCH/ISNUMBER/FILTER/SORT Formula
Like any formula, we will work our way logically from the inside-out.
Step 1 – SEARCH
The first step is to locate all entries in the Customer column that contain the text entered in cell B5 on the Report sheet.
=SEARCH(Report!B5, TableCustomer[Customer])This returns a result for each item in the Customer column. If a Customer contains the desired text, we see the character position at which the text occurs. Otherwise, we see a #VALUE error.
Step 2 – Convert Results to TRUE/FALSE Values
Next, we want to convert all the results that returned a number into TRUE and all errors into FALSE.
This can be achieved by nesting the SEARCH function within an ISNUMBER function.
=ISNUMBER(SEARCH(Report!B5, TableCustomer[Customer]))
Step 3 – Filter for TRUE Responses
Now we will filter the list to only display entries that contain TRUE as a response.
Since we don’t want a list of repeated TRUE responses, we want the list of Customers that correspond to a TRUE response, we will filter the Customer column against the previous T/F responses. If no match is found, we will display the message “Not Found”.
=FILTER(TableCustomer[Customer], ISNUMBER(SEARCH(Report!B5, TableCustomer[Customer])), “Not Found”)
Step 4 – Sort the Results
Not a required step, but a nice touch would be to sort the list in ascending order to make it easier for the user to locate their desired result.
=SORT(FILTER(TableCustomer[Customer], ISNUMBER(SEARCH(Report!B5, TableCustomer[Customer])), “Not Found”))
Implementing Data Validation
On the Report sheet, select cell B5 and go to Data (tab) -> Data Tools (group) -> Data Validation.
In the Data Validation dialog box, select List from the Allow drop-down and enter =MasterData!$D$2# in the Source field.
NOTE: The pointer in the SOURCE field is using the new Dynamic Array syntax to reference a spilled array, hence the “#” character at the end of the reference.
If we type something like “ro” in cell B5 of the Report sheet, we can next click the drop-down and see what Data Validation has discovered via our amazing formula.
Altering Data Validation Behavior
If we enter something new into cell B5, we are presented with a Data Validation Error.
This is because the new text does not conform to the previously derived list. We need to make Data Validation a bit more forgiving.
Return to the Data Validation dialog box and select the Error Alert tab. Clear the checkbox for “Show error alert after invalid data is entered”.
We are now able to enter new text in cell B5 which then produces new Data Validation lists for refined discovery and selection.
If we do not place any text in cell B5, the Data Validation list returns the entire list of customers.
If we enter text that does not occur in the MasterData – Customers list, we are presented with the “Not Found” message.
Why It Only Works for a Single Cell
A suspected solution to replicating this Data Validation feature is to place the original formula inside a Named Range called “DV” then use the “DV” reference in Data Validation.
Highlight and copy the original formula, then select Formulas (tab) -> Defined Names (group) -> Name Manager to create a new Named Range called “DV” that contains the formula.
If we delete the original formula and replace it with a reference to the newly created “DV”, we see it works as hoped.
Since the test reference to “DV” worked in a cell, let’s try it in Data Validation.
We are presented with the following error.
Creating Separate Formulas for Each Line Item
What if we created a separate formula for each potential line item?
We can’t simply fill the formula down successive cells because the spill ranges will overlap one another and create #SPILL! errors (remember this from before?)
Enhance the Formula with TRANSPOSE
What if instead of each formula’s results spill vertically (downward) we spill the results horizontally (rightward)?
If we nest the earlier formula within a TRANSPOSE function, we can achieve just such a result.
=TRANSPOSE(SORT(FILTER(TableCustomer[Customer], ISNUMBER(SEARCH(Report!B5, TableCustomer[Customer])), “Not Found”)))
Now we can fill the formula down the column for as many rows as we expect in our Report sheet.
This yields a separate Data Validation list for each cell.
The reason we see “Andre Cooper” and the other names to the right is because each repetition of the formula examines a different cell from the Report sheet. The cell that each repeated formula references is noted in the following image.
Since these cells are currently empty, the formula returns the entire list of names.
Updating Data Validation
Returning to the Report sheet, click the cell for the first desired dropdown (ex: B5). Update Data Validation to point to the corresponding custom list created in the previous step (ex: MasterData sheet, cell D2).
=MasterData!D2#NOTE: We are referencing cell D2 relatively, not absolutely. This is because we want the cell reference to change (i.e. D3, D4, D5, etc…) when we replicate Data Validation to the adjacent cells.
Repeat the Data Validation:
- Select the source cell (B5)
- Click Copy
- Highlight the destination cells (B6:B19)
- Right-click the highlighted area and select Paste Special…
- In the Paste Special dialog box, select Validation
- Click OK
Testing
Selecting cells, such as B6, we can type in text, click the drop-down list and see the results for our custom list.
Selecting B7 and entering different text yields different results.
As before, if we don’t enter any text and click the drop-down list, we are presented with all names from the source list.
If we enter text that does not appear in the source list, we are presented with the message “Not Found”.
Each drop-down list is tied to its own custom formula results.
Bonus Formula – The Amazing XLOOKUP Function
It would be nice to have the customer’s company name appear to the right of the customer name after the final name selection has been made.
On the Report sheet, select cell C5 and enter the following XLOOKUP formula:
=XLOOKUP(B5, TableCustomer[Customer], TableCustomer[Company], “”)Fill the XLOOKUP formula down Column C (i.e. C5:C19) and test.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
