Autonumbering outdent and indent separately

Angela Logie

My outdents are initiatives and my indents and milestones and would like to number my outdents INIT001 and so on, and my indents to be numbered as MILE0001 and so on...though I dont seem to be able to do this. I've tried having separate columns for this also one for Initiative Number and one for Milestone Number but the autonumber is applying to all rows...hopefully someone can help

Genevieve P.

Hi @Angela Logie

The way I would do this is to add a number of helper columns to the sheet.

1. Column to identify if a row is a Parent or Child
2. Auto-Number System column with just numbers
3. Rank column to rank the Auto-Number, based on if it's a Parent or Child, and add in the correct preface.

Formula in the Parent column:

=IF(COUNT(ANCESTORS(Task@row)) = 0, "INIT", "MILE")

See: ANCESTORS Function

Formula in the Unique ID column:

=IF(Parent@row = "INIT", "INIT" + RANKEQ(Auto@row, COLLECT(Auto:Auto, Parent:Parent, "INIT"), 1), "MILE" + RANKEQ(Auto@row, COLLECT(Auto:Auto, Parent:Parent, "MILE"), 1))

See: RANKEQ Function and COLLECT Function

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

Angela Logie

Oh this sounds great...I have added the Auto and Parent columns. Created the Uniqie ID column however the formula is showing as invalid operation...

Genevieve P.

Hi @Angela Logie

It looks like your Auto number column contains 0's leading up to the actual number. Leading 0's will translate the number into a text value (to keep the 0 showing) so it can't be evaluated with the RANKEQ to rank the Auto Number and produce a new number.

Does your process require the 0s?

If not, you can re-set an auto-number column to start back at 1 from the top of the sheet (see: Reset the Auto-Number Column)

Angela Logie

This worked a treat...you are a star!!! Thank you soooooo much!!!

Genevieve P.

You're welcome! I'm so glad it works for you.