Data Shuttle and Cell Linking
I have a destination sheet that uses data from cell links from different other sheets. I created a source sheet using Data Shuttle that runs on attachment. The workflow runs correctly, but the destination sheet doesn't update. It says that the cell link is invalid because it got deleted.
I need that destination sheet to be updated using that data shuttle feature but are Cell Link and Data Shuttle not compatible? The workflow that I have is to replace the data...should I use merge or something else?
Data Shuttle sheet: (csv file attached, this updates as it should)
Source sheet:
Thank you so much for your help and insight...this feature is very new to me so I'm still learning!
Savita
Best Answer
-
When you replace the data with Data Shuttle, it deletes out the previous rows and uploads completely new rows. This means that the cell which was linked to your other sheet is deleted and no longer exists in Smartsheet, which is why you're getting the error.
Instead of using a cell-link to pull the data across, I would suggest using a formula. The formula can look at the columns in general, versus locking on to one specific cell which will be replaced.
In this instance, I'd build out a few INDEX(MATCH formulas to look for a specific value in your BA resource column (such as "CRL"), then return what's in the column next to that value.
An INDEX(MATCH works like this:
=INDEX({Column with value to return}, MATCH("Value to match", {Column with value to match in other sheet}, 0))
For example, you could replace the cell-link that's currently displaying "447" with this:
=INDEX({Plasma/Blood/Bile/Urine column}, MATCH("CRL", {BA Resource Column}, 0))
You would need to change what you're looking for in the BA column for each of the cells in this row (so, change "CRL" to be "GENENTECH" for the other value, etc).
Then for your Tissues row, you'd need to delete out the initial column reference and replace it with one that looks to your second column:
=INDEX({Tissues column}, MATCH("CRL", {BA Resource Column}, 0))
Here are some Help Center articles that you may find useful: INDEX Function, MATCH Function, Cross-sheet formulas
Let me know if you're having any trouble building this out and I'm happy to help further.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
When you replace the data with Data Shuttle, it deletes out the previous rows and uploads completely new rows. This means that the cell which was linked to your other sheet is deleted and no longer exists in Smartsheet, which is why you're getting the error.
Instead of using a cell-link to pull the data across, I would suggest using a formula. The formula can look at the columns in general, versus locking on to one specific cell which will be replaced.
In this instance, I'd build out a few INDEX(MATCH formulas to look for a specific value in your BA resource column (such as "CRL"), then return what's in the column next to that value.
An INDEX(MATCH works like this:
=INDEX({Column with value to return}, MATCH("Value to match", {Column with value to match in other sheet}, 0))
For example, you could replace the cell-link that's currently displaying "447" with this:
=INDEX({Plasma/Blood/Bile/Urine column}, MATCH("CRL", {BA Resource Column}, 0))
You would need to change what you're looking for in the BA column for each of the cells in this row (so, change "CRL" to be "GENENTECH" for the other value, etc).
Then for your Tissues row, you'd need to delete out the initial column reference and replace it with one that looks to your second column:
=INDEX({Tissues column}, MATCH("CRL", {BA Resource Column}, 0))
Here are some Help Center articles that you may find useful: INDEX Function, MATCH Function, Cross-sheet formulas
Let me know if you're having any trouble building this out and I'm happy to help further.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi Genevieve,,
You're so amazing!! Thank you so much for your thoughtful and detailed answer...I am truly impressed with the community and how helpful everyone is.!
I will take your suggestions and try it out today...it should work...but I will definitely contact you if I get stuck.
Thanks again sooo much!
Sincerely,
Savita Ubhayakar
-
No problem at all! Hope it's going well. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi @Genevieve P I haven't yet tried it but will tackle it today! Thanks so much again!!
-
Hi @Genevieve P , your formula worked like a charm!! Thank you for the detailed and thoughtful response....I really appreciate your help and expertise!!
-
Thanks for letting me know, I'm so glad to hear it! It's my pleasure, I'm glad I could help. Please feel free to post again if you have other questions.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Happy Friday Smartsheet Geeks,
The INDEX formula function does not work for a date column that I am trying to cell link from my target file to other sheets in Smartsheet. I am integrating a Google Sheets file with Smartsheet using Data Shuttle as I need the dates from this target file to be cell linked to other project trackers in Smartsheet. I am losing these important dates every time data shuttle workflow runs. Can anyone help me on this please? Thanks in advance!
Ipshita Mukherjee
-
Hi @Ipshita
I saw your comment on this other post and answered you there! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives