I need a formula to create a Contract iD for 4 different org's that are listed in 1 column

I need to create a formula in a column that will in the end give me something like ATF021 or RDI231 or RD473

Each org, ATF, RDI and RD will have their own contract iD; so ATF021's next line item needs to be ATF022, 023. Then if the RDI org enters a line item, there contract iD will be RDI231, RDI232, RDI233 and so on. Same goes with the RD org. How would I set this up?

Tags:

Best Answers

  • Georgie
    Georgie Employee
    edited 11/13/24 Answer ✓

    Hi @csalci01,

    To ensure that your formula only populates the Contract ID column when the funding Org is “ATM”, you can wrap the formula provided by @jmyzk_cloudsmart_jp in an IF statement, so your formula would be:

    • =IF(Org@row = "ATM", Org@row + RIGHT([Zero Padding]# + MATCH(Row@row, COLLECT(Row:Row, Org:Org, Org@row)), LEN([Zero Padding]#)))

    However, when I tested this, I was unable to get that formula working, and the formula that worked for me was:

    • =IF(Org@row = "ATM", Org@row + RIGHT([Zero Padding]#, 2) + MATCH(Row@row, COLLECT(Row:Row, Org:Org, Org@row)))

    Do either of these work for you?

    Additionally, when a column has a space in the name, you can wrap the column name in square brackets [], e.g. for a column called “Funding Org”, you’d enter this as [Funding Org] within your formula, eg [Funding Org]:[Funding Org]. More information on this here.

    Hope that helps!

    Georgie

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • csalci01
    csalci01 ✭✭
    Answer ✓

    Thank you Georgie! This was great.

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @csalci01

    You can use the MATCH and COLLECT functions to get successive numbers for each organization.

    =Org@row + RIGHT([Zero Padding]# + MATCH(Row@row, COLLECT(Row:Row, Org:Org, Org@row)), LEN([Zero Padding]#))

    Alternatively, you can use the RANKEQ function instead of the MATCH function.

    RANKEQ(Row@row, COLLECT(Row:Row, Org:Org, Org@row), 1)

    The difference is that RANKEQ needs a unique number range, whereas MATCH works with any unique IDs.

    https://app.smartsheet.com/b/publish?EQBCT=77bb0a9219254122a6bcf672f21fd396

    The example above uses a Sheet Summary field to designate the Zero Padding, but you can incorporate it into the formula.

  • Thanks for the quick response! I am not able to get it to work though. I must be doing something wrong

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    @csalci01

    Please email or use this form to contact me so that I can share the demo sheet.

    https://app.smartsheet.com/b/form/20d1998f4fbd4e448f1080f684e0e27f (form link)

  • Hi J!

    Here is a snapshot of what I am trying to accomplish. Id id end up using your formula. I found that the column I was using in the formula needed to not have a space in the name for the formula to work.

    But now I have a new problem. I need to use the formula that you shared with me, thank you by the way, but I also need the other cells in the same column to not populate when the Funding Org is other than ATM. The Legacy Contract ID Column needs to remain blank. for the grey section (Contract ID) column to fill with a formula I already put in and pulls the data from Row ID into Contract ID.

    Or is it easier to convert the Legacy Contract ID column to a Column Formula with the 4 different Funding Org's? If so, How would I write and execute that formula? This is only for future Line items. All current Line Items need to stay as is with their current Contract ID.

    Thank thank you for your time on this. Much appreciated!!!

    CNS

  • Georgie
    Georgie Employee
    edited 11/13/24 Answer ✓

    Hi @csalci01,

    To ensure that your formula only populates the Contract ID column when the funding Org is “ATM”, you can wrap the formula provided by @jmyzk_cloudsmart_jp in an IF statement, so your formula would be:

    • =IF(Org@row = "ATM", Org@row + RIGHT([Zero Padding]# + MATCH(Row@row, COLLECT(Row:Row, Org:Org, Org@row)), LEN([Zero Padding]#)))

    However, when I tested this, I was unable to get that formula working, and the formula that worked for me was:

    • =IF(Org@row = "ATM", Org@row + RIGHT([Zero Padding]#, 2) + MATCH(Row@row, COLLECT(Row:Row, Org:Org, Org@row)))

    Do either of these work for you?

    Additionally, when a column has a space in the name, you can wrap the column name in square brackets [], e.g. for a column called “Funding Org”, you’d enter this as [Funding Org] within your formula, eg [Funding Org]:[Funding Org]. More information on this here.

    Hope that helps!

    Georgie

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • csalci01
    csalci01 ✭✭
    Answer ✓

    Thank you Georgie! This was great.

  • Georgie
    Georgie Employee

    No problem @csalci01 - glad I could help!

    Georgie

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!