Using unique IDs from one sheet in another

Options
Rob Cooper
edited 05/14/21 in Formulas and Functions

Hi,

Background - I have a project tracker with over 300 unique IDs. I want to create a separate sheet to work out the impact of a given project. I plan on indexing key information from the first sheet, adding in additional columns to work out the impact score and then index the final score back into the main sheet. This in theory will make it easier to control the scoring criteria and reduce the amount of hidden columns in the main sheet.


Problem - What formula do I use to identify and list all IDs from the first sheet into the second sheet? New IDs will be added all of the time and I want the second sheet to automatically pull the IDs over, figure out the scoring and return a value. The numbers will always be unique.

Best Answer

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

    Hi @Rob Cooper

    I hope you're well and safe!

    You can add a system column, auto-number, and then add the numbers pre-filled in the other sheet and link it with cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

    You can then use the same to collect the information back to the source sheet.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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.

Answers

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

    Hi @Rob Cooper

    I hope you're well and safe!

    You can add a system column, auto-number, and then add the numbers pre-filled in the other sheet and link it with cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

    You can then use the same to collect the information back to the source sheet.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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.

  • Rob Cooper
    Options

    Hi Andree,

    Thank you for your response. I am a little simple and slow when it comes to these things and can't see how this will work. My source data already has a system generated unique ID (below). It is this column I want to automatically show on a different sheet. Also, when new items are added to the source (new projects submitted) I want the new sheet to automatically show the new unique ID.

    Would what you proposed above work in this instance? I hate to ask, because you are helping me out here, but is it possible to show me a dummy query to show what I need to put in the new sheet?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!