Formula to add 1

Options

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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.

  • Detrie Zacharias
    Detrie Zacharias ✭✭✭✭✭
    Options

    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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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.

  • Detrie Zacharias
    Detrie Zacharias ✭✭✭✭✭
    Options

    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())


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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.

  • Detrie Zacharias
    Detrie Zacharias ✭✭✭✭✭
    Options

    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?

  • Jeana
    Jeana ✭✭✭✭✭✭
    edited 08/21/20
    Options

    @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!!


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!