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

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?


Tags:

Answers

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!