Array Formula - Google Sheets

0
votes

I have botched out a script I found on Google referring to array_constrain. The script should automatically fill the cell with the format S-"date"-001 in which will increment every time a new row is populated. The problem is the result doesn't display the letter S but the number zero. It displays as 0-06112020-001 where as it should be S-06112020-001. I've included the script below:

=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(LEN(A:A),IF(ROW(A:A)=1,"Bug No.",TEXT(ROW(H:H),"S-")&TEXT(A1:A,"ddmmyyy")&TEXT(ROW(H:H)-1,"-000")),)),500,1)
2
arraysgoogle-sheetsgoogle-sheets-formulastring-formattingarray-formulas

2 Answers

1
votes

try:

=ARRAY_CONSTRAIN(INDEX({"Bug No."; IF(A2:A="",,
 "S-"&TEXT(A2:A, "ddmmyyyy")&TEXT(ROW(H2:H)-1, "-000"))}), 500, 1)
0
votes

Change your formula to

=ARRAY_CONSTRAIN(ARRAYFORMULA(IF(LEN(A:A),IF(ROW(A:A)=1,"Bug No.","S-"&TEXT(A1:A,"ddmmyyy")&TEXT(ROW(H:H)-1,"-000")),)),500,1)

and see if that helps?