Dynamic arrays in tables. Anything new?

[rwleighjr]

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.

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.

 

[Fluff]

You cannot have dynamic spill ranges in a table. And if you limit each cell to a single value then it will be slower & less efficient, not to mention the fact that it will no longer be dynamic.
In short do not put dynamic spill formulae in a table.

 

[rwleighjr]

Thanks much for your reply, sir. I'm sorry to be late in sending a reply. I'm back to the forum after a number of years of being absent and my membership was tied to an old email address for which I couldn't remember the password. Couldn't figure out how to fix this so I just registered under a different login.

Yes, I knew in the past you couldn't have spilled ranges in tables. I just wondered if this had changed or there was a workaround so that I wouldn't have to rely on VBA to accomplish what I needed. Guess not.

 

[Peter_SSs]

my membership was tied to an old email address for which I couldn't remember the password. Couldn't figure out how to fix this so I just registered under a different login.

The Forum Rules (#7) do not permit two user accounts. I will send you (that is the new rwleigh account) a private conversation message about this. Please check your messages at the top right of the forum next to you user name.

 

[rwleighjr]

I inadvertently set up a 2nd account in violation of rule #7. I would like to delete my previous user account as it was tied to an old email address a bunch of years ago. I no longer have that password and I can't/couldn't set up a new password for it since the forgotten password method requires I use the old email address.

So please tell me how to delete the old user account. Thank you for your patience.

 

[Peter_SSs]

As I said ..

Please check your messages at the top right of the forum next to you user name.

 

[rwleighjr]

I was writing a reply when your message came in. Are we square now. Thank you for all your help.

 

[Peter_SSs]

Are we square now.

Yes we are.