Auto generate project number using formula

sasithwij

Hi,

I'm trying to create a formula that will auto populate the project number when a new project is created through a form. When a project is added through a form it appears at the top of the sheet. Our project numbering system is YY-XXX where YY is the year of the start date of the project and XXX is the project number which just adds 1 to the maximum value of the previous project number of that year. Below is an example of what it should look like.

Is there a formula I can create to auto populate this?

Thanks!

Paul Newcome

I would insert an auto-number column with no special formatting then use this column formula:

=RIGHT(YEAR([Project Start Date]@row), 2) + "-" + IF(COUNTIFS([Auto-Number Column]:[Auto-Number Column], @cell<= [Auto-Number Column]@row, [Project Start Date]:[Project Start Date], IFERROR(YEAR(@cell), 0) = YEAR([Project Start Date]@row)< 10, "00", IF(COUNTIFS([Auto-Number Column]:[Auto-Number Column], @cell<= [Auto-Number Column]@row, [Project Start Date]:[Project Start Date], IFERROR(YEAR(@cell), 0) = YEAR([Project Start Date]@row)< 100, "0", "")) + COUNTIFS([Auto-Number Column]:[Auto-Number Column], @cell<= [Auto-Number Column]@row, [Project Start Date]:[Project Start Date], IFERROR(YEAR(@cell), 0) = YEAR([Project Start Date]@row)