1

I am very new to creating formulas in a SP column, so hopefully I explain it accurately.

I have a requirement to have a custom number auto-generate to display (ACR Number) PF-IS-VH-17-000. 1st field cat.(2 options), 2nd = business unit, 3rd = location (3options)and then YY-sequential number (3 digits).

This is my formula:

=IF(Category="Project File","PF-IS-"&TEXT(Created,"yy")&CONCATENATE("-",REPT("0",MAX(0,3-LEN(ID))),ID),IF(Category="Change Control","CC-IS-"&TEXT(Created,"yy")&CONCATENATE("-",REPT("0",MAX(0,3-LEN(ID))),ID)))

My problem is I do not know how to get the 3rd field in there, the associated column in a CHOICE column with 3 locations to choose from. Please and thank you.

1

You can get the choice into the formula as similar as a text column.

Here is my formula with choice column.

=Location&TEXT(Created,"yy")

In the formula Location is a choice column and Created is an out of the box Created date column.

It displays the result as below.

enter image description here

Let me know if I am missing anything.

  • This? =IF(Category="Project File","PF-IS-"=Location&TEXT(Created,"yy")&CONCATENATE("-",REPT("0",MAX(0,3-LEN(ID))),ID),IF(Category="Change Control","CC-IS-"=Location&TEXT(Created,"yy")&CONCATENATE("-",REPT("0",MAX(0,3-LEN(ID))),ID))) – K. Boone Aug 3 '17 at 17:37
  • It gives me a ACR Number of "0" – K. Boone Aug 3 '17 at 17:38
  • try like this =IF(Category="Project File","PF-IS-"&Location&TEXT(Created,"yy")&CONCATENATE("-",R‌​EPT("0",MAX(0,3-LEN(‌​ID))),ID),IF(Categor‌​y="Change Control","CC-IS-"&Location&TEXT(Created,"yy")&CONCATENATE("-‌​",REPT("0",MAX(0,3-L‌​EN(ID))),ID))) – Venkat Konjeti Aug 3 '17 at 17:57
  • I hope Location is your choice field. If not replace with your field. – Venkat Konjeti Aug 3 '17 at 17:58
  • Yes, it is. However, I am receiving a syntax error. I am verifying placement of paren/commas/"" – K. Boone Aug 3 '17 at 18:03
1

Worked Great!

=IF(Category="Project File","PF-IS-"&Location&TEXT(Created,"-yy")&CONCATENATE("-",REPT("0",MAX(0,3-LEN(ID))),ID),IF(Category="Change Control","CC-IS-"&Location&TEXT(Created,"-yy")&CONCATENATE("-",REPT("0",MAX(0,3-LEN(ID))),ID)))
  • Please if this formula worked for you, Please don't forget to Mark your answer as the accepted answer to close this question, thanks! – Mohamed El-Qassas MVP Aug 30 '17 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.