Forum Discussion
Spill Error in Excel when using Table format.
===
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.
Regarding your reply statement, " You use FILTER() function which returns the spill into the cells." Where did I mention that the FILTER() function returned a #SPILL! into the cells?
Here is what I said I did:
1. Using dynamic Filter function I created a formula to create results in A2:A30.
Notice, I stated "create results in A2:A30" which means the FILTER() function DID NOT return a #SPILL! into the cells.
2. Highlighted Filter function results A2:A30 to Insert Table (clicked on Insert -> Table -> Create Table).
Note: NO WHERE in A2:A30 is #SPILL!.
Received #SPILL! Error in A2 AFTER I clicked on Insert -> Table -> Create Table. This #SPILL! is NOT from the Filter Function. The #SPILL! is from the attempt to create a table using the Filter Function results in A2:A30.
What do I want to do? Create a table containing the Filter results in A2:A30.
Thank you for your assistance.
Regards and thank you for your assistance.
- Thank you for the reply.
Hopefully soon. 🙂 - Thank you for the clarification and your assistance.
It doesn't matter in which order you do it: Tables and spill formulas do not work together. Hence the spill error.
Maybe this will change in the future.
That's misunderstanding. What dynamic array function returns is called spill. In your case FILTER() returns some values into A2:A30, entire object is spill. You may reference it as =A2# and you can't edit values within the spill, e.g. cell A10.
If function can't return spill into the grid, it shows #SPILL! error with some explanation why spill can't be returned - "the cell we need to spill data into isn't blank"
