How do I fix my Corrective Actions ID number to count only the ancestors created under the status?

xX4Zero4Xx

As of right now I have this as my formula

=IF(COUNT(ANCESTORS(Status@row)) < 1, "", YEAR([Date Generated]@row, 2) - 2000 + "-" + IF(COUNTIF(Status@row, Status@row) < 10, "000" + COUNTIF(Status@row, Status@row), IF(COUNTIF(Status@row, Status@row) < 100, "00" + COUNTIF(Status@row, Status@row), IF(COUNTIF(Status@row, Status@row) < 1000, "0" + COUNTIF(Status@row, Status@row)))))

Whenever I attempt to add a reference point for the count to begin (ie: Status$1: ) I receive a syntax error. The goal is to generate a unique ID code for every ancestor one level in.

Though it seems I may have the additional problem of once it needs moved to a separate lead ancestor the ID codes would shift. Is there a better way to go about what I am attempting?

The ID code needs to maintain its original formatting of "YY-####.##" (ex: 23-0001.01)

YY - (ex: 23) two digit year

####- (ex: 0001) Count number reset year to year

.##- (ex: .01) The iteration upon the current project (or ancestors of the original unique ID)

Would this be easier to utilize individually calculated columns and combined into one?

Genevieve P.

Hi @xX4Zero4Xx

Here's how you would reference "from the top row to this current row" in your formula:

COUNTIF(Status$1:Status@row, Status@row)

So in your case:

=IF(COUNT(ANCESTORS(Status@row)) < 1, "", YEAR([Date Generated]@row, 2) - 2000 + "-" + IF(COUNTIF(Status$1:Status@row, Status@row) < 10, "000" + COUNTIF(Status$1:Status@row, Status@row), IF(COUNTIF(Status$1:Status@row, Status@row) < 100, "00" + COUNTIF(Status$1:Status@row, Status@row), IF(COUNTIF(Status$1:Status@row, Status@row) < 1000, "0" + COUNTIF(Status$1:Status@row, Status@row)))))

Let me know if this resolves your issue!

Cheers,

Genevieve