Spill Error in Excel when using Table format.

@akolah1966 

PeterBartholomew1  wrote: As rows are added to the data table, the dynamic array formula will adjust accordingly if it involves a structured reference.

I just wanted to underscore the usefulness (the value) of that feature. It makes it easy to use a combination of an Excel Table, one or two Dynamic Arrays functions (notably UNIQUE, FILTER and SORT) to create Data Validation lists that are themselves dynamic and growing.

I've attached a simple example (created in response to another posting dealing with Data Validation, where the desire had been to create Data Validation lists where the second selection would change based on the first). This example uses a table containing first and last names; the user can select a first name (with data validation against a list of unique first names), followed by selection of last names only of those whose first name is the same as the first selection.   The sheet includes commentary on the formulas.

I use this kind of data validation structure in my personal budget and expense tracking spreadsheet, for expense categories and sub-categories. It allows me to add new sub-categories and major categories on an ad hoc basis.

It's also useful in generating summary reports, almost like your very own pivot table....

lots of applications.