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!
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")),""))}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.
Like a charm. Thanks so much!
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! 
