Data Shuttle and Cell Linking

Savita Ubhayakar

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

Genevieve P.

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

Genevieve P.

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

Savita Ubhayakar

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

Genevieve P.

Hi @Savita Ubhayakar

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

Savita Ubhayakar

Hi @Genevieve P I haven't yet tried it but will tackle it today! Thanks so much again!!

Savita Ubhayakar

Hi @Genevieve P , your formula worked like a charm!! Thank you for the detailed and thoughtful response....I really appreciate your help and expertise!!

Genevieve P.

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

Ipshita

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!

Genevieve P.

Hi @Ipshita

I saw your comment on this other post and answered you there! 🙂