Forum Discussion

Joseph715's avatar
Joseph715
Copper Contributor
Nov 13, 2023

Using a formula in a table and getting #spill

=TEXTJOIN("",TRUE,"NWJDC"&SEQUENCE(COUNTA($B2),,1000,1))

It appears I am able to continue the formula along in the table but I am not getting the correct result.

I want a sequential number to follow NWJDC1000, NWJDC1001...

 

I was receiving a #SPILL error however after adding the TEXTJOIN I am getting the result I would like but it fails to increase the value as it progresses.

 

  • djclements's avatar
    djclements
    Bronze Contributor

    Joseph715 Yes, dynamic array functions like SEQUENCE do not play well with structured tables. However, is this what you are trying to achieve in the IR Number column?

     

    ="NWJDC"&ROW(B2)-ROW($B$1)+999

     

    Or, if you want to use structured table references:

     

    ="NWJDC"&ROW(Table1[@])-ROW(Table1[#Headers])+999

     

    The results would look something like this:

     

    IR NumberDateTime
    NWJDC100011/14/20233:00 PM
    NWJDC100111/25/20235:30 PM
    NWJDC100211/30/20231:15 PM

Resources