Forum Discussion
SEQUENCE Function inside Excel Table
When I enter the SEQUENCE function within an Excel table I get the #SPILL! error. The specific usage is as follows: =SEQUENCE(COUNTA([Status])) where Status is a defined field name in the table. When I enter the same SEQUENCE formula outside of the table using COUNTA with the same Status field it works perfectly. Has anyone see/heard of this use case with SEQUENCE?
5 Replies
Your question already contains a potential solution. Entering the formula
= SEQUENCE(ROWS(Table1))
to the left of the table will generate an array of sequence numbers. As SergeiBaklan points out, these are not linked to the individual records so are not suitable for use as a primary key.
Tables are scalar-based and don't play nice with formulas resulting in a spill. What are you trying to accomplish with SEQUENCE?
Thanks for the reply Patrick. I'm just trying to create a auto-generated number for each record in my table. I know there are multiple ways to accomplish this but until now SEQUENCE has always worked well for me.
In addition to Patrick2788 that could be
=ROW()-ROW(Table1[[#Headers],[Id]])However, such index is not anchored to related rows. As soon as you sort the table, add or remove row in the middle, each concrete index will be related to another row.
