Custom AutoNumber?
Question on AutoNumber column capabilities. I want to add a column to my portfolio sheet that will assign a unique project ID, like the below example, to each project row. I have columns on the same sheet that designate project class & start date. I'd like to use the autonumber feature to look at the start date and project class of a project, and assign a unique id to it. Is getting to the below example, or something close to it possible? Anyone know how to do this?
Example of Unique Project ID: R1220
R = Renovation (Project Class)
12 = Project No. 12
20 = Year when project was approved (so that each year we could restart project numbers)
Answers

It is possible. Are you able to provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

I created a quick grid with dummy data below. Thank you for your help!

Ok. So I can tell that the leading letter is the first letter from the [Project Class] column.
I am assuming that the last portion of the ID is the year, but for R and I you have 2 digits and for C you have all 4. I want to confirm that this is correct?
Finally... How exactly are you determining the number in the middle section? I can't find a pattern/reference.

Sorry I had a typo!
Yes: the leading letter is the first letter from the [Project Class] column.
Yes: the last portion of the ID is the year, only need two digits
Middle section is where i want the auto number to come in, assigning each project its own number. Does that need to be a separate, hidden column, then combine all fields with a formula? or is there another way? Want the final [Project ID] result a unique ID for the project field, or row, on my portfolio sheet.
Thank you!

It doesn't need to be a separate column. I just want to make sure I understand the logic. I don't notice a pattern for it though.
You have
1
2
3
1
How exactly did you determine those numbers?

No pattern or logic. I was just typing in random dummy numbers. Was assuming the same number could be used more than once if its associated to a different Project Class. The middle number can be any number, as long as the end result is a Unique Project ID.

Ok. So how about this...
=LEFT([Project Class]@row + "" + COUNTIFS([Project Class]$1:[Project Class]@row, [Project Class]@row, [Start Date]$1:[Start Date]@row, YEAR(@cell) = YEAR([Start Date]@row)) + "" + RIGHT(YEAR([Start Date]@row), 2)
This will pull the first letter from the [Project Class]. Then it will give a sequential count of how many previous rows had that same [Project Class] in that same year. Finally it will pull the right two digits of the year.
So an example output of the above would be...
R120
R220
I120
C120
R119
R320
I220
C119
Does that work, or would you like something different for the middle portion?

That is perfect. Thank you so much for your help!

Happy to help! 👍️

Hello, I would like to add an auto custom number column to a sheet. The format is YYMMsequence in 3 numerical digits. We start the sequence over at 001 at the beginning of each month which is a piece to this function that I can't seem to get. Here's how far I've gotten:
=LEFT(YEAR(Created@row))+""+(MONTH(Created@row))+""+(COUNTIF(Inquiry@row)created)

@Paul Newcome you're such a formula/function guru, are you able to help with the above?

@Akellu Try something like this...
RIGTH(YEAR(Created@row), 2), + "" + IF(MONTH(Created@row) < 10, "0") + MONTH(Created@row) + "" + IF(COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row)) < 10, "00", IF(COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row)) < 100, "0")) + COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row))

Hi @Paul Newcome, thanks for your help. I copied your formula and still got Unparsable. I updated the column references, indicated by yellow dot so you can easily see. It's still not working. Any additional help or insight is greatly appreciated.

My apologies. I had a comma that snuck in where it didn't belong. Try this...
=RIGHT(YEAR(Created@row), 2) + "" + IF(MONTH(Created@row) < 10, "0") + MONTH(Created@row) + "" + IF(COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row)) < 10, "00", IF(COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row)) < 100, "0")) + COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row))

Paul maybe you can help me. I am trying to do the same autogenerating reference numbers using a column that it is like the project class (What is it?) and the reference date will be the created column date that automatically assigned by system when the item is added.
I followed the formula that you put it upper but it is giving #unparseable error. I appreciate as usual your help
=LEFT([What is it?]@row + "" + COUNTIFS([What is it?]$1:[ What is it?]@row, [What is it?]@row, [Created]$1:[ Created]@row, YEAR(@cell) = YEAR([Created]@row)) + "" + RIGHT(YEAR([Created]@row), 2)
Then what i am trying to get in that Auto Ref Test is RXXX21, if the What is it? is Issue the initial letter should be IXXX21 and if it is Audit should be AXXX21
Thanks in advance
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!