# Dynamic arrays in tables.  Anything new?



## rwleighjr (Dec 20, 2022)

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.xlsxABCDEFGHIJKLM234Table 1Array 1Table 256DATEABDATEABDATEAB712/10/2022506012/10/225060#SPILL!812/5/2022253012/5/222530#SPILL!912/1/2022151012/1/221510#SPILL!1011/30/2022251211/30/222512#SPILL!1111/25/202210511/25/22105#SPILL!1213TransCell FormulasRangeFormulaF7:H11F7=INDEX(tblTrans,SEQUENCE(ROWS(tblTrans[DATE])),XMATCH($F$6:$H$6,tblTrans[#Headers]))J7:J11J7=INDEX(tblTrans,SEQUENCE(ROWS(tblTrans[DATE])),XMATCH(F6:H6,tblTrans[#Headers]))Dynamic array formulas.


----------



## Fluff (Dec 21, 2022)

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 (Dec 21, 2022)

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 (Dec 21, 2022)

rwleigh said:


> 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 (Dec 21, 2022)

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 (Dec 21, 2022)

As I said ..


Peter_SSs said:


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


----------



## rwleighjr (Dec 21, 2022)

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


----------



## Peter_SSs (Dec 21, 2022)

rwleighjr said:


> Are we square now.


Yes we are.


----------

