Formula to add 1
I am trying to figure out a formula that will add 1 to the Parent [DMR #]
The below will Place a "1" at the end but I need to add count by one
Like this
DMR5575-1
DMR5575-2
DMR5575-3
=IF(Level1 = 0, [DMR #]1, PARENT([DMR #]1) + "-" + 1)
Answers
-
Hi Stavros,
I think we need one or two so-called helper columns to make it work.
What is your starting number? Is it DMR5575 or DMR5575-1?
I hope that helps!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andree.. I think that's the part I was missing. I'm not opposed to adding helper columns
DMR5575 is the starting number it is an autonumber
-
Can you share a screenshot of the structure?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
DMR# is an AutoNumber
Work Numbers is the field Im trying to populate (shown as I would like it calculate)
Primary is the primary
ANCESTORS is a Formula field =COUNT(ANCESTORS())
-
Try something like this.
Add a so-called helper column, Name it RowID
Add the following formula in that column:
=COUNT($Ancestors$1:$Ancestors@row)
Change the formula in the column Work Number to:
=IF(Ancestors@row = 0, [DMR #]@row, (IF(Ancestors@row = 0, COUNTIF(Ancestors$1:Ancestors@row, 1), "" + PARENT() + "" + COUNTIFS(Ancestors$1:Ancestors@row, Ancestors@row, RowID$1:RowID@row, >(PARENT(RowID@row))))))
Did it work as expected?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andrée
This works EXACTLY as expected. I'm trying to understand the formula.
Where in the formula does the RowID convert from the sequential number to "1" when a new DMR # is created?
-
@Andrée Starå Hi Andrée, I found this discussion and it's right along the lines of something I'm trying to do. I wondered if you could take a look at this similar scenario and shed some light on the formula?
Here's a screen shot. The DESIRED column is what I'm after, basically if Ancestors = 1 I want the Asset # to increment by one (add 1). This number should carry down to CHILDREN but GRANDCHILDREN (If Ancestors = 3) should display the Asset # - 1 or Asset # - 2, as shown.
Appreciate any help you can offer. I also don't have to use an Automated Number field. It could be all in the formula.
Here is your formula in the column Andrées formula - slightly modified:
=IF(Ancestors@row = 0, [Auto Asset #]@row, (IF(Ancestors@row = 0, COUNTIF(Ancestors$1:Ancestors@row, 1), "" + PARENT() + "" + "-" + COUNTIFS(Ancestors$1:Ancestors@row, Ancestors@row, [Row ID]$1:[Row ID]@row, >(PARENT([Row ID]@row))))))
Thanks!!
-
Hi @Jeana
Apologies, I missed your post.
Do you still need help, or did you solve it?
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!