Custom Auto Number Formula

MelBueno46

HI Community I need help creating a custom auto number formula using set prefex and a reference column. The DRN "number" or Identifier must populate as follows: ABCDE-DRN24-020T.

The prefix 'ABCDE-DRN' is absolute for all numbers and the '24' after DRN is referencing the FY Reference column year. The next 3 numbers after the dash for the year, would need to be the auto number portion and I need the sequence to start at 021 followed by the letter T at the end.

I am novice when it comes to complex formulas and I tried referencing other articles here, but no luck. Here is the formula I was trying to work with, but I honestly don't know what I'm doing so any help is welcome:
="NCSES-DRN"+ "-" + RIGHT(YEAR([FY Reference]45, 2)) + "-" + COUNTIFS([Auto number]:[Auto Number], @cell<= [Auto Number]@row + "-" + "T"

Please Help!

MelBueno46

Jesse G.

Hi Melbueno46,

I tried making the output you posted in the question above:
="NCSES-DRN" + RIGHT([FY Reference]@row, 2) + "-" + SUM(20, COUNTIFS([Auto Number]:[Auto Number], @cell <= [Auto Number]@row)) + "T"

The struggle I found was staring at 20 in the function SUM.
Could you give this formula a try and let me know?

Happy to assist you any further!

MelBueno46

Unfortunately no go. Still returns "Unparseable"

Jesse G.

could you show me your grid maybe?

Since for me I got the right output.

MelBueno46

When I put in the same calculation this is how it comes up. The DRN Temp is currently manual creation with nor formula. The Test column is where I am testing the formulas and I used the one you gave.

Jesse G.

so if I understand correctly you are not executing the formula on the same row?

Normally when you put the formula in the first row and make it a column formula, it should work.

MelBueno46

That I undertand but unfortunately I can't do it to the whole column or it will renumber all the existing items

I need it to add it in midway

Jesse G.

If there is text mid air the formula should work there too. But it's #UNPARSEABLE cuz there isn't data on the row.

Jesse G.

Just wanted to check, but did you find a fix?

MelBueno46

Hi Jesse, unfortunately still no 😔. Appreciate you trying though.

Jesse G.

So to break it down you need a formula starting in row 21 (With the number 20)? This won't be a column formula

the prefix NCSES-DRN is absolute, 24 is you year, 20 is your row number and an added T?

This is correctly?