Autonumbering arrayformula that skips a blank cell but maintains sequence - how?

I have the below formula in cell A1 to generate sequential event IDs (DS ID). I’m open to an improved version.

={"DS ID";ARRAYFORMULA(IF(X2:X<>"","DS-"&TEXT(ROW(X2:X)-1,"0000"),""))}

When an event is cancelled (e.g. DS-0011), the formula will skip a cell (A13) and the numbering restarts in A14 with DS-0013. I need for that to be DS-0012 instead. Possible?

Thanks!

snip

You can do it like this.

={"Event ID";ARRAYFORMULA(IF(B2:B=FALSE,"DS-"&(TEXT(COUNTIFS(B2:B,FALSE,ROW(A2:A),"<="&ROW(A2:A)),"0000")),""))}
1 Like

thank you @ThinhDinh! I’m halfway there as I already have a checkbox column. Only issue is it’s a checkbox component in from a Glide form and when it’s not ticked, cells in column B will not populate empty checkboxes which in turn breaks the formula. I remember reading that arrayformula/checkbox is not a thing but I’ll keep digging.

You can see how here how it stops working with the first new form submission in A18. All prior empty checkboxes were entered manually.

1 Like

I modified it to is not TRUE.

You can check the sheet here.

2 Likes

Like a charm. Thanks so much!

1 Like