Data Shuttle and Cell Linking

05/20/21
Accepted

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

  • Genevieve P.Genevieve P. admin
    Answer ✓

    Hi @Savita Ubhayakar

    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

Answers

  • Genevieve P.Genevieve P. admin
    Answer ✓

    Hi @Savita Ubhayakar

    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

  • 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

  • Hi @Savita Ubhayakar

    No problem at all! Hope it's going well. 🙂

  • 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!!

  • Hi @Savita Ubhayakar

    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

Sign In or Register to comment.