Forum Discussion

Copper Contributor

Aug 18, 2023

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...

excel

Formulas and Functions

Patrick2788

Silver Contributor

Aug 18, 2023

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

richmd
Copper Contributor
Aug 18, 2023
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.
- SergeiBaklan
  Diamond Contributor
  Aug 18, 2023
  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.
- Patrick2788
  Silver Contributor
  Aug 18, 2023
  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