generating a defined number of IDs

Options

Dear Smartsheet community,

I am trying to write a formula to generate a defined number of IDs.

In the screenshot below, if I enter 6 into the blue squared cell, I would like that 6 IDs are generated with a number incrementing at the end (ID_1, ..., ID_6).

if I enter 3, 3 labels are generated. (ID_1, ID_2, ID_3).

How could I proceed to generate these IDs based on a desired number ?

Thanks for your support!

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    edited 05/06/22
    Options

    What's the max number of these that you might want to create?

    There are a number of ways to do this.

    Create columns:

    Running: Create a list of numbers, 1-whatever your max is. I do this by putting 1 in row 1, 2 in row 2, and then grabbing both cells and dragging down to auto increment. You can also put 1 in the first row, and then =Running1 + 1 in row 2, and dragging row 2 down

    Placeholder: Put =Num$1 in row 1 and drag it down

    Num: This is where you put your 6

    ID: Create a column formula: =IF(Running@row <= Placeholder@row, "ID_" + IF(Running@row <= Placeholder@row, Running@row))

    You could also use a Sheet Summary field, and eliminate the Placeholder and Num fields:

    =IF(Running@row <= Num#, "ID_" + IF(Running@row <= Num#, Running@row))


  • Melanie_A
    Options

    The number will depend on the experiment but currently the max number will be 6 but could be less. We would like to get something dynamic, that can adapt to each experiment.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!