DISCLAIMER
As of this post’s date (June 2019), the functions discussed in this post are still in development and are not available to the general public. Currently, only users with an “Office Insiders” subscription to Office 365 posses these Dynamic Array functions. The functions will be arriving soon for all other Office 365 subscription levels.
Let’s Start Solving!
Our sample data set contains information about the sales date, selling agent, and sale amount.
Our objective is to allow the user to select a “from” date (cell G4) and a “to” data (cell H4) and have those dates govern the extraction of records from the table located in columns A through C, placing those extracted records in columns G through I.
This way, the data can be on a separate and possibly hidden sheet. The user will only see the date selection cells and the results list.
Building the Dropdown Lists
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:
=UNIQUE(A5:A53)
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:
=SORT(UNIQUE(A5:A53))
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:
=FILTER(M2#,M2#>G4,””)
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.
Extracting Records Based On the User’s Dates
The next step is to copy the records from the table in columns A through C where the sale date is equal to or between the two selected dates.
Begin by setting the dates as follows:
- From Date (cell G4) = 1/23/2019
- To Date (cell H4) = 1/28/2019
Next, select cell G7. Using our new friend FILTER, enter the following formula:
=FILTER(A5:C53,(A5:A53>=G4)*(A5:A53<=H4),”No records found”)
Observe that the generated list only includes records from the applicable dates. If the dates are changed, the list updates automatically.
Let’s make the dates and sale amounts more presentable by applying a Short Date style to the numbers in column G, and a Comma style (zero decimal places) to the numbers in column I.
Explaining the Previous Use of the Filter Function
Because we need to check for dates that are both greater than a specific date AND less than or equal to a specific date, we need to contain each test within their own sets of parentheses.
(A5:A53>=G4)*(A5:A53<=H4)The inclusion of the asterisk acts as the word AND, joining the two tests into a single test.
NOTE: The asterisk (along with the plus sign to indicate an OR operation) is used to create logical comparisons and is used by many higher-level functions in Excel such as INDEX, SUMPRODUCT, and AGGREGATE.
First Test Results
If we highlight the first test in the formula bar and press F9, we can see how the test evaluates each date in the list (column A dates) against the selected date (G4).
Where a date is greater than or equal to the selected date, the evaluation produces a TRUE response; all other responses are FALSE.
(Don’t forget to press CTRL-Z or click UNDO to restore your formula to its original state.)
Second Test Results
If we highlight the second test in the formula bar and press F9, we can see how the test evaluates each date in the list (column A dates) against the selected date (H4).
Where a date is less than or equal to the selected date, the evaluation produces a TRUE response; all other responses are FALSE.
(Don’t forget to press CTRL-Z or click UNDO to restore your formula to its original state.)
TRUE/FALSE Interpretation
Excel will interpret any TRUE response as a 1 and any FALSE response as a 0 (zero). If we pair the tests together and multiply the values, we will produce 1 of 4 results.
Any test that generates a 1 (TRUE) is included in the filter. Any test that generates a 0 (FALSE) is excluded from the filter.
With this logic, the only records that will be captured by the filter are those records with sale dates that resulted in TRUE from both the “from” date test and the “to” date test.
Making the Source Data Dynamic
To ensure we incorporate any additional records from our table in columns A through C in our formulas, we need to “upgrade” our plain table of data to a proper Excel Data Table.
Click anywhere inside the plain table and press CTRL-T. Click the OK button in the Create Table dialog box to finalize the request.
Although you can leave the color of the table as-is, we will remove the colors by clicking the bottom of the three buttons to the right of the In-Ribbon Gallery and select Clear at the bottom of the gallery.
Since the table will most likely by hidden from the user, we don’t need to burden the computer with additional computations for colors when we won’t seen them in the result.
Testing the Dynamic Nature of the Formulas
To ensure that newly added records appear in the dropdown lists, navigate to the bottom of the table of sales and add a few more records.
Set the “from date/to date” range of dates to include dates of the newly added records. Notice they appear in the secondary results table.
Don’t Forget
As of this post’s date (June 2019), the functions discussed in this post are still in development and are not available to the general public. Currently, only users with an “Office Insiders” subscription to Office 365 posses these Dynamic Array functions. The functions will be arriving soon for all other Office 365 subscription levels.
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.
