SEQUENCE Function inside Excel Table

[richmd]

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?

[#2]

Tables are scalar-based and don't play nice with formulas resulting in a spill. What are you trying to accomplish with SEQUENCE?

[#3]

@Patrick2788 

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.

[#4]

@richmd 

Depending on where the table starts in your sheet row-wise, you could use  the ROW function.

Let's say the table starts at row 1 with the header and records following start at row 2. Then the formula would be:

=ROW()-1

[#5]

@richmd 

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.

[#6]

@richmd 

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 @Sergei Baklan points out, these are not linked to the individual records so are not suitable for use as a primary key.