Mar 23 2022 10:31 AM
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?
Mar 23 2022 12:28 PM
Mar 23 2022 03:31 PM
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.