Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Assign a Program Code, without using Auto Number

    ✭✭
    in Formulas and Functions

    I am already using the Auto Number column, but I want to generate a unique [Program Code]@row for each new program added to a sheet, starting with 5,000. However, I don't want to assign a Program Code to a program added to the sheet, if the [Program Name}@row is a duplicate of an already existing program.

    Program Code | Program Name

    5000 | Psychology

    5001 | Sociology

    5002 | Theology

    none | Sociology

    New programs are added to the bottom of the list (when the row is copied from another sheet). No Program existed at first, so the first one (Psychology) was assigned 5000 (as a result of the formula). Then, each new program added was 500x + 1. However, when Sociology was submitted again, no Program Code is assigned because this was a duplicate submission.

    I tried using the COUNTIFS to check for duplicate program names, then MAX to determine highest program code that existed. But I was getting #CIRCULAR and then #BLOCKED errors, even when I tried using a Helper column:

    [H-Program Code] Helper column formula:
    =COUNTIFS([Program Name]:[Program Name], [Program Name]@row)

    [Program Code] column formula:

    =IF([H-Program Code]@row > 1, "", IF(COUNT([Program Code]:[Program Code]) = 0, 5000, MAX([Program Code]:[Program Code]) + 1))

    Thanks in advance!

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • Community Champion
      edited 01/31/25

      Hi @aschneiderheinze1025,

      Try the solution described below:

      Auto Number : A column with the auto-number property, starting at 5000.

      Program Code : Column formula :

      =IF([Auto Number]@row = MIN(COLLECT([Auto Number]:[Auto Number], [Program Name]:[Program Name], [Program Name]@row)), [Auto Number]@row)

      Logic : If the Program Name appears for first time then Program Code will be set to the Auto Number; else it remains blank.

      Hope this helps.

      image.png

      Gia Thinh Technology - Smartsheet Solution Partner.
      Email : thinh.huynh@giathinh.tech

      · Share on FacebookShare on Twitter
    • ✭✭

      @Gia Thinh Thanks for the suggestion, I had mentioned though that I was already using Auto Number in the sheet for something else, so I wouldn't be able to have another one in this solution to generate the 5000. Would the integrity of the numbering remain if the rows are sorted? Or if the duplicate program rows were deleted?

      · Share on FacebookShare on Twitter
    • Community Champion

      My solution will not be affected by row sorting or the deletion of duplicate program rows

      Gia Thinh Technology - Smartsheet Solution Partner.
      Email : thinh.huynh@giathinh.tech

      · Share on FacebookShare on Twitter
    • ✭✭

      In order to see what the program code is for a program, rather than leave it blank, my inclination would be to set-up a separate master data sheet that captures all unique programs, and assigns them a code with an auto-number. Then, the Program Code can pull in via a cell reference based on the value in the Program Name field (dropdown). Just a thought for consideration.

      · Share on FacebookShare on Twitter
    • ✭✭

      @Gia Thinh Yes, and that's great. However, can the formula be adapted so I don't lose numbers when there are duplicate Program Names. I see in your example the duplicate Sociology programs don't have a Program Code, but they have an Auto Number. So, those Auto Numbers (5003, 5004) never become Program Codes. Is there a way to still use 5003 and 5004, just skipping those rows with duplicate Program Names (i.e., Sociology 2 would actually be 5003, not 5005)?

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 01/31/25

      @aschneiderheinze1025

      You mentioned "New programs are added to the bottom of the list (when the row is copied from another sheet)..."

      If you are using a Copy-row automation workflow in the source sheet, you can add a condition checking If the Program Name is not a duplicate" then "run the Copy-row action" . This will ensure only unique Program Names are copied to the target sheet and the Auto Number will be numbered continuously.

      Gia Thinh Technology - Smartsheet Solution Partner.
      Email : thinh.huynh@giathinh.tech

      · Share on FacebookShare on Twitter
    • ✭✭

      @Gia Thinh How do you include a "check that a program name is not a duplicate" in the new sheet before copying the row from the source sheet to the new sheet? I guess I could do a cross-sheet reference formula in the source sheet to do this check, and return the result in a checkbox, so that if that box is checked in the source sheet, the automation (in the source sheet) will not do the copy.

      · Share on FacebookShare on Twitter
    • Community Champion

      Yes, you are correct.

      Gia Thinh Technology - Smartsheet Solution Partner.
      Email : thinh.huynh@giathinh.tech

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions