I would like to pull data from one smartsheet into another

SHOOD

I'm sorry to be asking this question, I've been struggling for several days. My background is sql against rational databases. In sql, I would like to join together 2 tables and grab the data in column Z from the second table when the values match in both tables on a specific column (not providing the value in the statement as it changes on rows).

What Smartsheet formula will accomplish this? I create a new column for the answer in the Smartsheet I own.

Thank you for your help!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315606#Comment_315606

Yes, inconsistent data can make this a challenge. You might have to do some manual data cleanup for this to work correctly.

I'm wondering why you have the same ranges here though:

=INDEX({WashU_Workset Playbook Range 2}, MATCH([Ref ID for Activity when ready for unit test]@row, {WashU_Workset Playbook Range 2}, 0))

There should be two different ranges on the remote sheet. The range after "INDEX" should contain the values you want to pull from the remote sheet into your sheet, based on identifying the rows in that remote sheet that have a value in a different column which matches the value in your sheet. For example:

=INDEX({Item Master Hierarchy Value}, MATCH(PartNumber@row, {Item Master PartNumber}, 0))

This says: give me the hierarchy value from Item Master sheet, where the PartNumber on this row matches the PartNumber from the Item Master sheet.

Jeff Reisman

The best method I have found is to use INDEX/MATCH. The Logic is:

Find the value in Sheet2 Column Z, where the value in Sheet1 Column A matches the value in Sheet2 Column A.

Syntax:

=INDEX(range1, MATCH(value, range2, 0))

When you start typing your formula and get to =INDEX( Smartsheet will help you build it. Click on Reference Another Sheet in order to select the range (column) in the other sheet which contains the values you want. Same thing for the remote range in the MATCH portion of the formula.

=INDEX({Sheet2 ColumnZ}, MATCH(ColumnA@row, {Sheet2 ColumnA}, 0))

SHOOD

Hi Jeff!

Thank you for your help!

This is what I came up with based on your response:

=INDEX({WashU_Workset Playbook Range 2}, MATCH([Ref ID for Activity when ready for unit test]@row, {WashU_Workset Playbook Range 2}, 0))

I appreciate your response! I have looked at the data in the 2 Smartsheets and it doesn't appear to be consistent enough to do an auto calc. Some rows have nothing in the Ref ID for Activity when ready for unit test column, while others have multiple values in the Ref ID for Activity when ready for unit test column. I think I would have to include a default value if nothing is returned as well as loop through the rows that have multiple values.

Unless I'm missing an easy solution?

Thanks Jeff!

Susan

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315606#Comment_315606

Yes, inconsistent data can make this a challenge. You might have to do some manual data cleanup for this to work correctly.

I'm wondering why you have the same ranges here though:

=INDEX({WashU_Workset Playbook Range 2}, MATCH([Ref ID for Activity when ready for unit test]@row, {WashU_Workset Playbook Range 2}, 0))

There should be two different ranges on the remote sheet. The range after "INDEX" should contain the values you want to pull from the remote sheet into your sheet, based on identifying the rows in that remote sheet that have a value in a different column which matches the value in your sheet. For example:

=INDEX({Item Master Hierarchy Value}, MATCH(PartNumber@row, {Item Master PartNumber}, 0))

This says: give me the hierarchy value from Item Master sheet, where the PartNumber on this row matches the PartNumber from the Item Master sheet.

SHOOD

Thanks again Jeff for catching my error in the ranges. I corrected that but I'm still not seeing what I want due to the data. I appreciate all of your help!