Reference previous row in an array formula-CodePudding

I'm trying to create a unique ID in a google sheet array formula. The ID should be made up by the first 3 letters of a supplier name in a column the year(from a timestamp column) the month(from a timestamp column) a serial number starting with "01" and then resets at when the timestamp reaches a new month.

For the serial number to work, I need to reference the previous row. I'm not sure how to do that in an array. Any ideas will be greatly appreciated. The header row is as follows:

={"Purchase Order ID";ARRAYFORMULA(IF(ISBLANK(E2:E),"",left(upper(SUBSTITUTE(I2:I," ","")),3)&TEXT(E2:E,"yy")&TEXT(E2:E,"mm")&TEXT(IF(MONTH(E2:E)=MONTH(E2:E),VALUE(RIGHT(D2,2)) 1,1),"00")))}

CodePudding user response：

Formula for you

={"Purchase Order ID";ARRAYFORMULA(IF(ISBLANK(E2:E),"",left(upper(SUBSTITUTE(I2:I," ","")),3)&TEXT(E2:E,"yy")&TEXT(E2:E,"mm")&TEXT(COUNTIFS(TEXT(E2:E,"mm"),TEXT(E2:E,"mm"),ROW(E2:E),"<="&ROW(E2:E)),"00")))}