excel in 365

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3167917%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3Eexcel%20in%20365%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3167917%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20have%20a%20spill%20error%2C%20%22we%20can't%20spill%20within%20a%20table%22.%20I%20am%20working%20with%20a%20new%20sheet%20and%20a%20form%20I%20have%20created.%20When%20I%20enter%20my%20data%20the%20first%20line%20comes%20out%20correctly.%20When%20I%20add%20a%20new%20set%20of%20data%20the%20%23Spill%20error%20occurs%20with%20the%20above%20message.%20How%20do%20I%20fix%20this%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3167917%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3168220%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20in%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3168220%22%20slang%3D%22en-US%22%3EI%20should%20also%20mention%20I%20am%20doing%20a%20calculation%20in%20the%20last%20column%20where%20this%20error%20is%20occurring.%3C%2FLINGO-BODY%3E
New Contributor

I have a spill error, "we can't spill within a table". I am working with a new sheet and a form I have created. When I enter my data the first line comes out correctly. When I add a new set of data the #Spill error occurs with the above message. How do I fix this?

 

5 Replies
I should also mention I am doing a calculation in the last column where this error is occurring.

@BruceFin I suspect you are entering a formula with a dynamic array function inside a structured table.

What formula are you entering? Can you upload a screenshot showing the problem?

@BruceFin 

One of two lines of development may be appropriate.  

The first is to leave the calculation within the last column of the Table but make sure that the structured references are to single cells by using the "@" notation.

image.png

The alternative is to move outside the table and create a dynamic array formula that references entire table columns.

Yes I was in an array function but my table was not structured properly. I believe that was why I was getting the message, "we can't spill within a table". I started over and used ctrl T to create the table. Doing this now allows me to add data with a form and enter formulas for calculations. Now when I enter the data the formulas auto calc and I am no longer getting the #spill error.
I am new to this aspect of using Excel. I have always just tabbed through my columns to enter the data but using a form makes it much easier I have discovered. Thanks for your interest and help.
Yes I was in an array function but my table was not structured properly. I believe that was why I was getting the message, "we can't spill within a table". I started over and used ctrl T to create the table. Doing this now allows me to add data with a form and enter formulas for calculations. Now when I enter the data the formulas auto calc and I am no longer getting the #spill error.
I am new to this aspect of using Excel. I have always just tabbed through my columns to enter the data but using a form makes it much easier I have discovered. Thanks for your interest and help. My problem was far more basic and due to a lack of knowledge and understanding.