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?

Find more posts tagged with

Formulas

Accepted answers

Georgie

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

csalci01

Thank you Georgie! This was great.

All comments

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.

csalci01

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

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)

csalci01

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

Hi @csalci01,

=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?

Hope that helps!

Georgie

csalci01

Thank you Georgie! This was great.

Georgie

No problem @csalci01 - glad I could help!

Georgie

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.