• 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

C

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
 
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.
 
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.
 
Fluff13 said:
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.
Click to expand...
So doing a FILTER formula that generates the descending cells until it gets to the last return is a Spill range?
 
Fluff13 said:
Can you supply a sample workbook.
Click to expand...
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

  • Sample Capacity Planning.xlsx
    327.9 KB · Views: 6
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:
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?
 
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.
 
Fluff13 said:
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.
Click to expand...
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.
 
You must log in or register to reply here.
Back
Top