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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
13
  • 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
    Commented Aug 3, 2017 at 17:37
  • It gives me a ACR Number of "0"
    – K. Boone
    Commented Aug 3, 2017 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
    Commented Aug 3, 2017 at 17:57
  • I hope Location is your choice field. If not replace with your field.
    – Venkat Konjeti
    Commented Aug 3, 2017 at 17:58
  • Yes, it is. However, I am receiving a syntax error. I am verifying placement of paren/commas/""
    – K. Boone
    Commented Aug 3, 2017 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)))
Improve this answer
1
  • 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 ♦
    Commented Aug 30, 2017 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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