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?
Best Answers
-
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, for example, 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
-
Thank you Georgie! This was great.
Answers
-
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.
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
-
Please email or use this form to contact me so that I can share the demo sheet.
(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
-
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, for example, 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
-
Thank you Georgie! This was great.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 142 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!