Forum Discussion
Spill Error in Excel when using Table format.
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.
mathetesHi Mathetes, Do not understand how your Data Validation reply fixes the issue.
Used Filter function formula to create results in A2:A30. Now highlighted A2:A30 to Insert Table and received #SPILL! in A2.
Issue: How do I insert/create a table (Table1) for A2:A30?
Table1 Refers To =Sheet1!$A$2:$A$30
Regards
You wrote:
Do not understand how your Data Validation reply fixes the issue.
Used Filter function formula to create results in A2:A30. Now highlighted A2:A30 to Insert Table and received #SPILL! in A2.
Issue: How do I insert/create a table (Table1) for A2:A30?
Table1 Refers To =Sheet1!$A$2:$A$30
And I don't really understand your question, why you might've expected my Data Validation reply to be applicable to what you're seeking to do. It was a demonstration of something else that did help the original poster.
If the response from SergeiBaklan has met your need, that's great.
If not, let me suggest that you start a new thread rather than building on this one from six months ago, and give a more complete description of what you are facing, if possible attaching a copy of your Excel file.
Hi Mathetes,
Thank you for the reply.
Regarding, "why you might've expected my Data Validation reply to be applicable to what you're seeking to do."
akolah1966 mentioned: I keep getting the #Spill Error when using the dynamic Xlookup function. This only happens when I use the Table format i.e.does not happen when I change the Table to a Range.
I am using the dynamic FILTER function and also received the #Spill Error. If I convert the Filter results in A2:A30 to a range and then highlight A2:A30 to Insert Table, the table is created.
Regarding:
If the question is how insert spill into the table or build table on the top of the spill - no way. One or another.
If the question is how to use spill in data validation - just A2#.No. Neither of those statements is my question.
The spill occurred when I (1) highlighted the dynamic Filter function results in A2:A30 then (2) try to create a table using the dynamic Filter function results in A2:A30 by clicking on Insert -> Table -> Create Table.
Here is what I did:
1. Using dynamic Filter function I created a formula to create results in A2:A30.
2. Highlighted A2:A30 to Insert Table (clicked on Insert -> Table -> Create Table).Received #SPILL! Error in A2. Hence, want to create a table of the Filter results in A2:A30.
Hence, why I originally stated:
Issue: How do I insert/create a table (Table1) for A2:A30?
Table1 Refers To =Sheet1!$A$2:$A$30
If the question is how insert spill into the table or build table on the top of the spill - no way. One or another.
If the question is how to use spill in data validation - just A2#.
- Hi Sergei Baklan,
Thank you for the reply.
Regarding:
If the question is how insert spill into the table or build table on the top of the spill - no way. One or another.
If the question is how to use spill in data validation - just A2#.
No. Neither of those statements is my question.
My question was "How do I insert/create a table (Table1) for A2:A30? Table1 Refers To =Sheet1!$A$2:$A$30" No where in my question is "how insert spill" nor "how to use spill".
The spill occurred when I (1) highlighted the dynamic Filter function results in A2:A30 then (2) try to create a table using the dynamic Filter function results in A2:A30 by clicking on Insert -> Table -> Create Table.
Here is what I did:
1. Using dynamic Filter function I created a formula to create results in A2:A30.
2. Highlighted A2:A30 to Insert Table (clicked on Insert -> Table -> Create Table).
Received #SPILL! Error in A2. Hence, want to create a table of the Filter results in A2:A30.
Regards and thank you for your assistance.===
If the question is how insert spill into the table or build table on the top of the spill - no way. One or another.
If the question is how to use spill in data validation - just A2#.
No. Neither of those statements is my question.===
But that's exactly what you do. You use FILTER() function which returns the spill into the cells. After that you try to build the table based on the cells where the spill is already placed. As result you have #SPILL! error.
