Having trouble linking a sheet. Can someone please help?

Options

Hi all, This used to work for me but now it doesn't. I had sheet #2 below linked to the Employee name and T&L columns but when I was having issues I unlinked it, now I can't get it to work again. Every time I highlight the Employee name and T&L columns in the main sheet, click the Link symbol, then highlight the total Employee and T&L columns in #2, it actually copies the names and pastes them into the main sheet. The way I had it set up before, it would update the T&L automatically depending on the employee name. I cannot figure out how I messed this up. Any ideas? Thanks so much! Terri


Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Tjmarget

    There are two differences here. You have the first part of the INDEX set up correctly, but the MATCH function works a little differently.

    The MATCH looks at one cell and searches for that value in one column.

    So, it will look for the Name in the Cell to the left, and search through the Name column in the other sheet.

    • This means that where you have:  {Here I highlighted both columns on the T&L sheet}
    • This should actually be: {Highlight only the Employee name column on the T&L sheet}

    Additionally, where you have:

    • MATCH([T&L sheet Range 2])@row

    This should be:

    • MATCH([Employee Name]@row,

    Note that there are none of (these brackets) around the cell reference, you just want to click on the cell that contains the name you're searching for.

    Then you apply this same formula to the entire column as a Column Formula, so it checks each cell in each row and finds the Match based on the Name to bring back the T&L.

    Using your syntax, here's the example:

    =INDEX({highlight the T&L column on the T&L Sheet}, MATCH([Click the Employee Name CELL]@row, {Highlight only the Employee name column on the T&L sheet}, 0))


    Let me know if this works, now!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Options

    Hi @Tjmarget

    Instead of cell-links, which would simply copy the information over (as you found), it sounds like you used to have a formula on your Sheet 1 which used Sheet 2 as a reference sheet to grab the correct value.

    This could have been either a VLOOKUP or INDEX(MATCH. I would personally recommend setting up a cross-sheet INDEX(MATCH to get this information.

    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))


    So in your case, something like this:

    =INDEX({T&L Sheet 2}, MATCH([Employee Name]@row, {Employee Name Sheet 2}, 0))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Tjmarget
    Options

    Hi Genevieve,

    Thank you so much for your response. I am trying this and keep getting the Unparseable error. I was trying to accomplish the formula above by clicking on parts of the T&L sheet.

    =INDEX({Here I highlighted the T&L column on the T&L Sheet}, MATCH([Here I highlighted the Employee name column]@row, {Here I highlighted both columns on the T&L sheet}, 0))

    It looks like this:

    =INDEX({T&L sheet Range 4}, MATCH([T&L sheet Range 2])@row, {T&L sheet Range 7}, 0))

    Am I not using the right links in the sheet?

    Thanks again!

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Tjmarget

    There are two differences here. You have the first part of the INDEX set up correctly, but the MATCH function works a little differently.

    The MATCH looks at one cell and searches for that value in one column.

    So, it will look for the Name in the Cell to the left, and search through the Name column in the other sheet.

    • This means that where you have:  {Here I highlighted both columns on the T&L sheet}
    • This should actually be: {Highlight only the Employee name column on the T&L sheet}

    Additionally, where you have:

    • MATCH([T&L sheet Range 2])@row

    This should be:

    • MATCH([Employee Name]@row,

    Note that there are none of (these brackets) around the cell reference, you just want to click on the cell that contains the name you're searching for.

    Then you apply this same formula to the entire column as a Column Formula, so it checks each cell in each row and finds the Match based on the Name to bring back the T&L.

    Using your syntax, here's the example:

    =INDEX({highlight the T&L column on the T&L Sheet}, MATCH([Click the Employee Name CELL]@row, {Highlight only the Employee name column on the T&L sheet}, 0))


    Let me know if this works, now!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Tjmarget
    Options

    That was it! Thank you so much for helping me in a way I could understand. You have saved me a ton of stress! :)

  • Genevieve P.
    Options

    No problem! I'm glad I could help. 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now