Won't Convert to Column Formula

Options

Trying to use a formula for autonumbering that will start over the fill number every year. Formula works well in a cell, but I can't get it to convert to a column formula

=IFERROR(RIGHT(YEAR(Date@row), 2) + RIGHT(COUNTIF(Date1:Date@row, YEAR(@cell) = YEAR(Date@row)) + 10000, 3), "")

Error message comes up that the column formula syntax isn't quite right, see our help article for more information

Any help would be appreciated!

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @dan.purcell

    I hope you're well and safe!

    It doesn't work because you're referencing a specific cell.

    =IFERROR(RIGHT(YEAR(Date@row), 2) + RIGHT(COUNTIF(Date1:Date@row, YEAR(@cell) = YEAR(Date@row)) + 10000, 3), "")

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    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.

  • Victoria_Indimar
    Victoria_Indimar ✭✭✭✭✭
    Options

    Hi Dan! If your only issue is getting it to convert to column formula, I think it's because of this section of the formula: COUNTIF(Date1:Date@row, YEAR(@cell)

    It should work if you change your range to the full column: COUNTIF(Date:Date, YEAR(@cell)


    Let me know if that helps!

  • dan.purcell
    Options

    Thank you Andree & Victoria. Unfortunately changing the section of the formula to COUNTIF(Date:Date, YEAR(@cell) made the formula #UNPARSEABLE. Is there anything else I can try to convert this formula into a column formula? Is there a better formula I can use? Thank you for your help!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @dan.purcell

    Happy to help!

    Did you try this?

    =IFERROR(RIGHT(YEAR(Date@row), 2) + RIGHT(COUNTIF(Date:Date@row, YEAR(@cell) = YEAR(Date@row)) + 10000, 3), "")

    Did that work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    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.

  • dan.purcell
    Options

    Andree, that formula did not work. #UNPARESEABLE response in cell. Thank you for your help!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 02/17/23
    Options

    @dan.purcell

    I forgot to remove one part. Sorry about that!

    This should work now.

    =IFERROR(RIGHT(YEAR(Date@row), 2) + RIGHT(COUNTIF(Date@row, YEAR(@cell) = YEAR(Date@row)) + 10000, 3), "")

    Did that work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    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.

  • dan.purcell
    Options

    Thanks Andree!! It can be converted to a column formula now, but it won't count? My goal is to create an autonumbering system in the primary column of the sheet that counts the previous entries with the same "year" in the date column. So when a new year starts, the prefix will change. Am I heading down the right path with this formula?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!