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?



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.


Like a charm. Thanks so much!

1 Like

Can I do it with Glide table? I have a table containing orders that are automatically numbered progressively. I need a system to reset the numbering from a new year and start again from order number 1. @ThinhDinh

How do you currently have your auto numbering set up?

With lookup and row index. @Jeff_Hager

Ok perfect, I was hoping you were using that method. I think if you create a Relation that links a year in that row to itself, then you can point your lookup column to that relation. Everything else should just work and I think the numbers will start from 1 for each year.


Thanks @Jeff_Hager
It works like a charm, better than a balm! :smiley:

1 Like