Is there a possibility to connect different cells between two sheets?

D.Koutroulis
D.Koutroulis ✭✭
edited 02/27/23 in Smartsheet Basics

I have two different sheets. One has a collumn with "areas" as texts and a collumn with "teams" which are again text. While working I put manually which team works for each area. But in a new sheet I need to put the area name and take the team name, as a result, automatically. Does anyone has an idea on this?

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @D.Koutroulis

    This is absolutely doable with a cross sheet VLOOKUP. Suppose you have your first sheet with data like this:

    In a second sheet, you can have the Area and Team columns again, with the following formula in the Team column:

    =VLOOKUP(Area@row, {Areas & Teams Range 1}, 2, false)

    To do the cross sheet bit - in the { } brackets, click this button when entering the formula:

    Then find the first sheet and highlight the columns you're after (in this case it is simple as there are only 2):

    Then click the Insert reference button you can see in the bottom right of the open window.

    You can then use the second sheet to have the team looked up from the area:

    The blue arrows in the "Team lookup" column indicate the data is coming from elsewhere, and the grey arrows on the other sheet show the data is going out to another sheet.

    Hope this is of some assistance; if you've any questions etc. then just ask! 😊

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 02/27/23

    Hi @D.Koutroulis

    I hope you're well and safe!

    You could use 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. (I recommend the INDEX/MATCH combination)

    To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

    Would that work/help?

    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:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @D.Koutroulis

    This is absolutely doable with a cross sheet VLOOKUP. Suppose you have your first sheet with data like this:

    In a second sheet, you can have the Area and Team columns again, with the following formula in the Team column:

    =VLOOKUP(Area@row, {Areas & Teams Range 1}, 2, false)

    To do the cross sheet bit - in the { } brackets, click this button when entering the formula:

    Then find the first sheet and highlight the columns you're after (in this case it is simple as there are only 2):

    Then click the Insert reference button you can see in the bottom right of the open window.

    You can then use the second sheet to have the team looked up from the area:

    The blue arrows in the "Team lookup" column indicate the data is coming from elsewhere, and the grey arrows on the other sheet show the data is going out to another sheet.

    Hope this is of some assistance; if you've any questions etc. then just ask! 😊

  • Thank you all! It worked! 😁

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

    @D.Koutroulis

    Excellent!

    Happy to help!

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. 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:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.