Auto-Numbering Parents, But Not Children

Nicole J

I need to set up a means of having "auto numbers" (use the next number in the sequence) for "parent rows", but not "children rows". Is this possible?

jmyzk_cloudsmart_jp

Hi @Nicole J

[ANC] =COUNT(ANCESTORS())
[PAN] =IF(ANC@row = 0, MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], ANC:ANC, 0), 0))

https://app.smartsheet.com/sheets/2CMgjqQfQmjhhQHXVxcGfp9PMgG68hJMjXpC2w51?view=grid (Link to the published demo sheet)

jmyzk_cloudsmart_jp

Hi @Nicole J

The solution below uses sheet summary fields, but you can replace them with the actual values.

=Prefix# + "-" + RIGHT([Zero Sup]# + [Parent Auto Number]@row, [ZS Len]#) + Suffix#

We can use the RIGHT function for zero suppression.

jmyzk_cloudsmart_jp

Hi @Nicole J

[ANC] =COUNT(ANCESTORS())
[PAN] =IF(ANC@row = 0, MATCH([Row ID]@row, COLLECT([Row ID]:[Row ID], ANC:ANC, 0), 0))

https://app.smartsheet.com/sheets/2CMgjqQfQmjhhQHXVxcGfp9PMgG68hJMjXpC2w51?view=grid (Link to the published demo sheet)

Nicole J

Thank you, so much!! This works great. Can you help me out with the [PAN] formula to:

• auto-lead with "Client - "
• display in six digits (000001, 000002, 000003, etc.)

So the [PAN] would spit out: Client - 000001, Client - 000002, Client - 000003, etc.

Many thanks.

jmyzk_cloudsmart_jp

Hi @Nicole J

The solution below uses sheet summary fields, but you can replace them with the actual values.

=Prefix# + "-" + RIGHT([Zero Sup]# + [Parent Auto Number]@row, [ZS Len]#) + Suffix#

We can use the RIGHT function for zero suppression.

Nicole J

Bingo. Thank you.

jmyzk_cloudsmart_jp

Happy to help!😁