Forum Discussion
Joseph715
Nov 13, 2023Copper Contributor
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.
- djclementsBronze 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 Number Date Time NWJDC1000 11/14/2023 3:00 PM NWJDC1001 11/25/2023 5:30 PM NWJDC1002 11/30/2023 1:15 PM