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.
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.
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.