Forum Discussion
Spill Error in Excel when using Table format.
Hi, 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 tried looking it ...
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.
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.