Has anyone come up with a way around the limitation of having dynamic arrays within tables?
In the attached example, I can have a dynamic array (Array 1) using SEQUENCE to mirror Table 1. But, as you’d expect, I encounter the SPILL error when I try to apply the formula to a table (Table 2). I’m seen how the implicit intersection operator can overcome spill errors but that might not translate to dynamic arrays. And I’ve tried that @ operator seven ways from Sunday without success. Any help would greatly appreciated. Thank you and Happy Holidays.
In the attached example, I can have a dynamic array (Array 1) using SEQUENCE to mirror Table 1. But, as you’d expect, I encounter the SPILL error when I try to apply the formula to a table (Table 2). I’m seen how the implicit intersection operator can overcome spill errors but that might not translate to dynamic arrays. And I’ve tried that @ operator seven ways from Sunday without success. Any help would greatly appreciated. Thank you and Happy Holidays.
SPILL Problem.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
2 | |||||||||||||||
3 | |||||||||||||||
4 | Table 1 | Array 1 | Table 2 | ||||||||||||
5 | |||||||||||||||
6 | DATE | A | B | DATE | A | B | DATE | A | B | ||||||
7 | 12/10/2022 | 50 | 60 | 12/10/22 | 50 | 60 | #SPILL! | ||||||||
8 | 12/5/2022 | 25 | 30 | 12/5/22 | 25 | 30 | #SPILL! | ||||||||
9 | 12/1/2022 | 15 | 10 | 12/1/22 | 15 | 10 | #SPILL! | ||||||||
10 | 11/30/2022 | 25 | 12 | 11/30/22 | 25 | 12 | #SPILL! | ||||||||
11 | 11/25/2022 | 10 | 5 | 11/25/22 | 10 | 5 | #SPILL! | ||||||||
12 | |||||||||||||||
13 | |||||||||||||||
Trans |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F7:H11 | F7 | =INDEX(tblTrans,SEQUENCE(ROWS(tblTrans[DATE])),XMATCH($F$6:$H$6,tblTrans[#Headers])) |
J7:J11 | J7 | =INDEX(tblTrans,SEQUENCE(ROWS(tblTrans[DATE])),XMATCH(F6:H6,tblTrans[#Headers])) |
Dynamic array formulas. |