number entered to reflect a job name/text

Options
nctd
nctd
edited 11/04/22 in Formulas and Functions

hey!

I was hoping to create a formula that gives me, in a column, a text that generates, from a number entered (1-46) based off of another sheet. i need to enter jobs done by each person via a number ID basically

Here is what I'm trying to do..

  • Value equal to 1, when entered, would show "Head" and so on. there are 46 jobs.

I would like there to be multiple dropdowns. for example, if i have carl coleman, and he has a team job of 1 (head), i would like to simply type a 1, then have it show as a text of the job (head in this case) instead of a number. then i would like to be able to do it again in the same cell and have each job he has performed and so on. attached is the data. :)



Thank you ever so much for your help!

Answers

  • Julio S.
    Julio S. Moderator
    edited 11/12/22
    Options

    Hi @nctd ,

    What you intend could be accomplished in different ways. I'm providing two possible solutions:

    • You can add numeric values to each job in the dropdown list, this way you'd only need to type the number so that the complete value autofills:



    • You may add an additional Helper Column to include each ID and create a cross-sheet Formula in the target Column where the jobs need to be filled along the line of: =INDEX({Jobs}, MATCH(ID@row, {IDs}, 0)) This way, Jobs will be auto-populated by the formula each time that you add an ID number in the correspondent Column.



    Note that you'll need to create a Column Formula for this to work as expected:

    I hope that these ideas can be of help.

    Cheers!

    Julio

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!