Custom Auto Number Formula
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: ABCDEDRN24020T.
The prefix 'ABCDEDRN' 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:
="NCSESDRN"+ "" + RIGHT(YEAR([FY Reference]45, 2)) + "" + COUNTIFS([Auto number]:[Auto Number], @cell<= [Auto Number]@row + "" + "T"
Please Help!
Answers


Hi Melbueno46,
I tried making the output you posted in the question above:
="NCSESDRN" + 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! 
Unfortunately no go. Still returns "Unparseable"

could you show me your grid maybe?
Since for me I got the right output.

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.

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.

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

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

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

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

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 NCSESDRN is absolute, 24 is you year, 20 is your row number and an added T?
This is correctly?
Help Article Resources
Categories
Check out the Formula Handbook template!