• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula not calculating in new table

cmf735

Member
I've had this problem a few times when trying to create a formula in a table. It just shows up as a text and does not calculate. If I place the same formula in a regular cell it works perfectly fine. Does anyone know why this occurs and how to avoid it?
74951
 

cmf735

Member
Now the formula keeps coming back as #SPILL!, but I don't have that issue in a regular cell. Any idea why that might be? I can't share the table because it's too big to upload.
 

Fluff13

Active Member
A Spill range is where you put a formula in one cell & it "spills" into neighboring cells.
Whilst it works in a normal cell, it cannot work inside a table & so you get a #SPILL error.
 

cmf735

Member
A Spill range is where you put a formula in one cell & it "spills" into neighboring cells.
Whilst it works in a normal cell, it cannot work inside a table & so you get a #SPILL error.
So doing a FILTER formula that generates the descending cells until it gets to the last return is a Spill range?
 

cmf735

Member
Can you supply a sample workbook.
This isn't the whole sheet but the main data that I'm working with. I'm trying to build the foundation for the "Woburn" sheet. I want to build the same style sheet as "Production Capacity" sheet, but only pulling "Current Status" that contains "Woburn". I then have to go back into the "Production Capacity" and exclude anything with "Woburn" in it. I feel like any formula I'm going to want to use are going to be SPILL ranges.
 

Attachments

Peter Bartholomew

Well-Known Member
The formulas that you would want to use in a table are ones which return a single scalar value within each record.
Once the results returned are arrays and are not in one to one correspondence with the records of the original table, it is time to move the formula away from the table. Instead of structured references created by the table, you move to dynamic array notation of a cell (direct reference or named) followed by the '#' symbol.
 
Last edited:

cmf735

Member
So the way I did it was I used the FILTER formula and just created it off to the side and then in the table I took the first cell and equaled it to the cell with the formula and then everything else auto filled across the sheet. I'm trying to take the Production Capacity sheet and exclude matching with anything that equals "Woburn Kitting In Process". Does anyone know a good formula that excludes a specific text?
 

Fluff13

Active Member
If you just want to filter the TWV_20 table, why not just use
=FILTER(TWV_20,TWV_20[STATUS]="Woburn Kitting In Process")
Although you will need to get rid of the #N/A errors in that table.
 

cmf735

Member
If you just want to filter the TWV_20 table, why not just use
=FILTER(TWV_20,TWV_20[STATUS]="Woburn Kitting In Process")
Although you will need to get rid of the #N/A errors in that table.
I'm trying to pull in all statuses but ignore Woburn. The formula I used was =FILTER(Table_owssvr[JO'#],(Table_owssvr[Current Status]="INV To Be Kit")+(Table_owssvr[Current Status]="Kitting In Process")+(Table_owssvr[Current Status]="Production Ready")etc...,"None") I was just wondering if there was a quicker/shorter way of getting the same result by just ignoring Woburn instead of typing out every status except for Woburn.
 
Top