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

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. ☺️
Best Answer
-
=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.
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I would suggest an INDEX/MATCH.
=INDEX({Column To Pull From}, MATCH([Item #]@row, {Other Sheet Item Number}, 0))
-
@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)
-
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.
-
=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.
-
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.
-
@Paul Newcome Perfect stuff.. or in redneck terms.. Hell Yea !!
Thank ya sir!!
-
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?
-
@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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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 !!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
-
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:
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.6K Get Help
- 434 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 507 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!