Copy specific columns from one sheet to another based on a value that equals on both sheets

DHarris

Morning/Afternoon Everyone!

I have been looking for a solution and have found some really great ideas/advice, but unfortunately, none that are actually the same as mine.

At the moment, working with (2) sheets (eventually maybe 3) that have a column identified as an "Item #". This item number will be created within SHEET-A. SHEET-B also has a column called "Item #" and has pre set values that match the actual number or the item. SHEET-B has several columns that has data contained within each which provide information about that item.

When an item is created within SHEET-A, it places the item # entered by the end user and IF this "Item #" matches the same "Item #" value within SHEET-B, then I wish to have specific columns from SHEET-B to be copied into columns within SHEET-A.

I do hope this makes sense. After a few hours now of researching for the proper process of doing this, I just had to finally give up and reach out to the professionals. If this has already been answered, please help a newcomer find the proper URL to complete this. ☺️

Paul Newcome

=INDEX({Column To Pull From}, MATCH([Item #]@row, {Other Sheet Item Number}, 0))

The INDEX function allows you to specify a range to pull from in the first portion and which row to pull from in the second portion of the function. If you are only pulling from a single column, then you do not need to use the third portion of the INDEX function.

MATCH will output a number based on where within a range the text to search for was found. When evaluating a single column, it will output the row number the match was made on.

The number output by the MATCH function serves as the row number for the INDEX function.

You will have two separate ranges. The first range is the column housing the data you want to pull over. The second range is the column housing the data to match on. In this case, the second range would be the Item # column as that is what you want to match on to bring over the data from a separate column.

So it is not pulling over the Item #. It is matching on the Item # to determine which row to pull from the first range.

Try plugging it in, creating the cross sheet references as indicated in the sample formula and see if it works.

Andrée Starå

Hi @DHarris

I hope you're well and safe!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

I would suggest an INDEX/MATCH.

=INDEX({Column To Pull From}, MATCH([Item #]@row, {Other Sheet Item Number}, 0))

DHarris

@Andrée Starå I think your answer is hinting around to the solution.. I could be wrong, but the numbers are already created automatically. The ITEM # is already there on both sheets. The request would be IF the values match at "tem #", then copy the specific cell at row/column of from SHEET-B to a specified cell at row/column of SHEET-A.

@Paul Newcome Definitely closer, or maybe I am not reading it correctly. This would obviously be in a "helper" column or whatnot, entered within SHEET-B, but it seems your function copies the "Item #" to the other sheet? Am I reading that wrong? (BTW.. common for me to read things wrong, just trying to understand how to apply your function)

DHarris

Maybe this image will help with my thoughts.

SHEET-B is the source of data and SHEET-A is the destination. "ITEM #" column is auto filled already. If they match, then the data on ITEM #1 of the source should populate the data that matches the ITEM #1 of the destination.

Paul Newcome

=INDEX({Column To Pull From}, MATCH([Item #]@row, {Other Sheet Item Number}, 0))

The INDEX function allows you to specify a range to pull from in the first portion and which row to pull from in the second portion of the function. If you are only pulling from a single column, then you do not need to use the third portion of the INDEX function.

MATCH will output a number based on where within a range the text to search for was found. When evaluating a single column, it will output the row number the match was made on.

The number output by the MATCH function serves as the row number for the INDEX function.

You will have two separate ranges. The first range is the column housing the data you want to pull over. The second range is the column housing the data to match on. In this case, the second range would be the Item # column as that is what you want to match on to bring over the data from a separate column.

So it is not pulling over the Item #. It is matching on the Item # to determine which row to pull from the first range.

Try plugging it in, creating the cross sheet references as indicated in the sample formula and see if it works.

DHarris

Thanks for your response @Paul Newcome. I believe I understand what you mean now and will try this out. Let ya know shortly.. again, thanks for your responses.

DHarris

@Paul Newcome Perfect stuff.. or in redneck terms.. Hell Yea !!

Thank ya sir!!

DHarris

WELLLLL.... too quick on the finish I guess.

I will say, this does in fact pull data from one sheet to another, but the matching of the "Item #" function is not really working correctly. This function is pulling data in from "Item #" 1 for the entire column in the destination sheet. If the "Item #" = 21, it is still using the same description as "Item #" 1.

Thoughts on what I messed up?

DHarris

@Paul Newcome Forgot to provide the function used for this. Based off of the screen shot above:

=INDEX({SHEET-B Range 1}, MATCH([Item #]@row, 0))

This was placed in a column where the description should be inserted into from SHEET-B based on if the item number matches. But again, all the data coming over to SHEET-A matches only what is in the lead row. In my case, it is "Item #" 1.

Andrée Starå

@DHarris

The structure would look something like this. (you're missing the bolded range)

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row, {ColumnWithTheValueToMatchAgainsTheCell}, 0))

Make sense?

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

DHarris

WOOT WOOT!!! This definitely worked. Man... incredible. Somewhat familiar with Excel, but Smartsheet... I'm getting there.

Thanks @Andrée Starå and @Paul Newcome !!

Phenomenal work !!

Andrée Starå

@DHarris

Excellent!

You're more than welcome!

Paul Newcome

Happy to help. 👍️

MTodd

I am trying to use the Index Match method above, but i keep getting a "#NO MATCH" message in the box. Below are images of my formula, and the sheet references:

Genevieve P.

Hi @MTodd

It looks like your Case Numbers in one sheet are displayed as Text values (on the left side of the cell) whereas in your current sheet with the formula the Case Numbers are entered and displayed a Numerical (on the right side of the cell).

What happens if you try and match the Case Number value + ""?

=INDEX({IssueTrackerOwner}, MATCH([Case Number]@row + "", {.... etc

Cheers,

Genevieve