To build the “from” and “to” dropdown lists in a way that is dynamic requires the construction of data preparation lists. These lists will perform additional manipulations of the dates prior to displaying the dates in the dropdown lists.
Building the “From Date” List
If we were to use a traditional Data Validation dropdown list to display the dates in column A, we would see many of the dates repeated.
We need to not only create the list but have the list display only one of each date from the column A source.
To generate the list of unique dates, we will use the UNIQUE function.
To understand the UNIQUE function, the syntax is as follows:
UNIQUE(array, [by_col], [occurs_once])
- array – is the range or array from which to return unique values.
- [by_col] – is a logical value indicating how to compare; By row = FALSE or omitted; By column = TRUE.
- [occur_once] – is a logical value: only return unique values that occur once = TRUE; include all unique values = FALSE or omitted.
Select cell M2 and enter the below formula:
The resultant list does not look like dates because the cell formatting of the original column A dates does not carry over to our unique list.
As a solution to this formatting issue, we will select cells M2:N54 and apply a Short Date style. We are selecting more cells than we have dates because we anticipate adding more dates later.
The list appears to be sorted.
This is only because our original list was in chronological order. If the list was in some other sorted order, we would need to sort the list.
Remember, we want this to be fully dynamic. Let’s solve this little issue by incorporating the Excel SORT function.
To understand the SORT function, the syntax is as follows:
SORT(array, [sort_index], [sort_order], [by_col])
- array – is the range, or array to sort.
- [sort_index] – is number indicating the row or column to sort by.
- [sort_order] – is a number indicating the desired sort order; 1 for ascending order (default), -1 for descending order.
- [by_col] – is a logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column.
Select cell M2 and update the formula as illustrated below:
We can now utilize the traditional Data Validation tool to turn the helper table into a dropdown list.
Select cell G4 and select Data (tab) -> Data Tools (group) -> Data Validation (top button).
In the Data Validation dialog box, select the Settings tab. From the Allow dropdown list, select List. In the Source field, enter =$M$2#.
The # (pound sign/hashtag symbol) is a new indicator to tell Excel to select the “spill area”; the area directly below cell M2 that contains the additional dates. As the “spill area” grows and shrinks, the Data Validation list will adjust to match.
We can also add a simple input message to give the user some direction in the use of the form.
Building the “To Date” List
We need the second list of dates to be only those dates that occur AFTER the selected date in the “from” date list.
To generate the list of unique dates that occur AFTER the selected “from” date, we will use the FILTER function.
To understand the FILTER function, the syntax is as follows:
FILTER(array, include, [if_empty])
- array – are the cells containing the data. If the cells being examined are part of a “spilled” array, select only the first cell in the array followed with a “#” symbol (ex: M2#).
- include – are the cells containing the data and the match criteria. As with the array variable, If the cells being examined are part of a “spilled” array, select only the first cell in the array followed with a “#” symbol (ex: M2#>G4).
- [if_empty] – indicates what should be displayed in the event the list contains no items. In our example, we will display nothing by entering two double-quotes. This indicates “empty text”.
Select cell N2 and enter the below formula:
We now have a filtered list of dates from the first preparation table (column M) that only displays dates after the date selection in cell G4. The second list doesn’t need to be sorted because the sorting was performed on the “from” dates list and sorting carries over to the “to” dates list.
Now we’ll build a second dropdown list using the Data Validation tool using the helper table in column N.
Select cell H4 and select Data (tab) -> Data Tools (group) -> Data Validation (top button).
In the Data Validation dialog box, select the Settings tab. From the Allow dropdown list, select List. In the Source field, enter =$N$2#.
Now we have our second dropdown list with valid dates after the selected “from” date.