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)

Tags:

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭
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

• ✭✭✭✭✭✭
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.

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

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭
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?

• ✭✭✭✭✭✭
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!!

• ✭✭✭✭✭✭
Options

Hi @Jeana

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

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!