Having trouble linking a sheet. Can someone please help?
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That was it! Thank you so much for helping me in a way I could understand. You have saved me a ton of stress! :)
-
No problem! I'm glad I could help. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives