Table formulas

Occasional Contributor

I'm currently using the following formulas to pull data from A to B based on a condition, then from B to C based on a condition, then finally from C to D based on a condition:


A to B = =FILTER(INDEX(Applicants2,SEQUENCE(ROWS(Applicants2)),{1,13,12,11}),Applicants2[Cleared Backround]="Yes")


B to C = =FILTER(INDEX('Hired 2022'!A3:F67,SEQUENCE(ROWS('Hired 2022'!A3:F67)),{1,4,3,2}),'Hired 2022'!F3:F67="Yes")


C to D = =FILTER(INDEX(Training,SEQUENCE(ROWS(Training)),{2,4,33}),Training[Authorized on Battery Changer]="Yes")


You can see that the 1st and 3rd formulas are pulling from tables while the 2nd does not. The 3rd formula is pulling from a duplicate worksheet where the data in formula 2 populates to, except the duplicate worksheet I have kept as a table. I did this so I could easily add more formulas based off the table references instead of having to constantly click and drag. My question is how can I alter my formula so that it spills all the required data AND I keep my data as a table? Is this even possible?

2 Replies
There is a lot here but I believe the basic question is if you can spill data into a table and as far as I know the answer is no.
That said, you can just reference the spill range(s) using the # operator and so although it isn't a table if you add formulas that refer to the location of the array formula (e.g. A2#) then that formula will be applied to the entire spill range.
Also, a little tip: in each of these formulas you are doing an INDEX( range, all rows, select cols) and using SEQUENCE(ROWS(range)) but you should be able to just leave that parameter blank like INDEX(range, , select cols)


Not having a copy of the spreadsheet, I haven't looked at the formulas in any great detail.  My preferred option, however, is to place all user input and data imports within Tables that are capable of growing and also provides structured references.

Conversely, my preferred option for any calculation beyond the most trivial 'one value per record' is to move to dynamic arrays in which the spill range follows the data.

As for formatting, I find black values in white cells meets nearly all requirements.  The grid is unnecessary and a minimum of horizontal trim lines define the ranges visually.  The little fly speck in the corner of the table is a sufficient cue in itself.