• 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


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

Filter - Dynamic Array and Tables

Is there a way for Dynamic Array formulas, i.e., Filter to play nicely with tables? If you look at the REPORT worksheet in the sample attached, you'll see Column A has the Filter, dynamic array formula outside of the table range of Columns B, C and D. This works perfectly, except you can't sort.
So, I created a second table in Columns G-J and entered the dynamic array in Column G, which throws a #SPILL! error.

I read that to avoid spill errors, you need to change from Explicit Cell References, i.e., =IFERROR(INDEX(STAGE,MATCH($A2,SHOPORDNO,0)),"") to Structured Cell References, i.e., =IFERROR(INDEX(MASTER[STAGE],MATCH($G2,MASTER[SHOP ORDER NUMBER],0)),""). So, I kept the Explicit Cell References in Columns B-D and the Structured Cell References in G-J. I'm still getting the #SPILL! error in Column G.

Thank you in advance!



Well-Known Member
You're not using the powerful functionality of pivot tables.
See attached cell A10 of the REPORT sheet where the data is sorted ascending.
No named ranges nor vba required. It took less than 5 minutes to set up.
There's just one unknown for me, and that's if there'll ever be more than one row in the MASTER table containing the same SHOP ORDER NUMBER, STAGE and SO combination. If so the pivot table will add those rows' PO AMOUNTs together. I've added a temporary Count column in the pivot table just to convince myself that there is only one row of each combination in your data. If you can state there there can only ever be one row in the MASTER table with that combination then there's no need for that confirmatory Count column. If you can't state that, then we can tweak.

What version of Excel are you using?; it can be made much more user friendly.