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 wit…

I modified it to is not TRUE. [image] You can check the sheet here. Thinh Dinh's lab V2.0 Robert Test Project,Task,Project,Task,Combined A,siLxzsQURZK3QGl0Zhqg7w, yLz8ofPiQrC8Y6BYfpm.CQ,A,siLxzsQURZK3QGl0Zhqg7w,A - siLxzsQURZK3QGl0Zhqg7w jRzXZcVgQruv37w-gFQ6Kg, zp5bp3uIQoWwJYAH0a-vgQ…

You can do it like this. [image] ={"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 …

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:

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

Ask for Help

ThinhDinh September 23, 2020, 2:23am 4

I modified it to is not TRUE.

You can check the sheet here.

Topic		Replies	Views
Issue ARRAYFORMULA & new item Ask for Help	9	374	January 17, 2024
ArrayFormula to apply checklist cell Ask for Help	7	411	July 25, 2020
Arrayformula storage issue Report a Bug	23	1935	February 4, 2024
Using ARRAYFORMULA()'s in Row 1 of your Spreadsheet Project Showcase	6	4569	August 8, 2020
ARRAYFORMULA problem Report a Bug	5	389	July 17, 2020

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

Related topics