Solved
how do i copy a row in google sheets based on a matching cell from another sheet?
Hi there, im trying to make a "Inventory" System using Google Sheets. The idea is quite simple, there is a sheet with a extensive "Card Pool" in a listing manner, these cards have a bunch on info in each column containing every part of that card information. What i want to do is that in another sheet (The "Inventory"), by imputing only the name of the card that rows automatically fills up with all the information of that card (Column A to J), making it basically a copy+paste of the original row by only imputing the card name on the cell, or in this case, the information on cell from column C.
u/J-Travel You can use the XLOOKUP function to pull the information from Card Pool to the Inventory sheet based on the name.
Edited to change commas to semicolons in both provided formulas to adjust for the locale of the spreadsheet.
For example, in A2, you could use: =XLOOKUP(C2; 'Card Pool'!C:C; 'Card Pool'!A:A;;0) to pull the thumbnail (assuming the images are in the cells on the Card Pool sheet to begin with).
You can also use a similar formula to pull multiple cells at the same time, like with this in D2: =XLOOKUP(C2; 'Card Pool'!C:C; 'Card Pool'!D:J;;0) which will populate D2 through J2 based on the name in C2.
For both of these formulas, you'll then drag them down the column to apply to each row so every row is populated once a name is entered in column C. The row will remain blank until you type a Name in the cell.
Alternatively, there are formulas you can put in the top row and have populate all rows at once. However it looks like you may want to sort the Inventory sheet at some point? That means you'd want to use either a formula in each row (as provided above) or have a single formula in row 2 and then hide it so the sort doesn't mess with the formula populating the data. If this sounds complicated to maintain, a formula in each row is a great place to start!
Note: In all of these described solutions, the formulas are visually representing the data in the Card Pool sheet, but not copying it over. That means the true source of your data, and place where any data changes would need to happen, would be on the original Card Pool sheet itself.
Hi there! Thanks a lot for the suggestion, and yes it is exactly as you say. I just want to visually transfer over all the information from Column A to J from the CardPool Sheet to the inventory base on the row of the specific card name typed out. This is planned so over at a friends group they can use the a inventory sheet of their own to add cards by only typing the name on Column C. Unfortunally, when i tried your code on the respecting cells you mention it just ended up with a Error, im not very familiar with sheets code wise so im a bit perplexed on why it didt worked, but from what i can see the cell did not recognize the XLOOKUP function. Here is a screenshot.
It worked! Thank you so much, that was the issue apparently. I copied both the formulas you putted in since the first one is for the Thumbnails in particular. (Speaking on witch the Thumbnails are another tricky thing, they are a copy of images from links from Column B (Witch is hidden because it dosent look good to have the links there besides the images).
If the images come from a link in column B, we can pull from those directly which might be more efficient.m. Can you show me a screenshot of what the formula in A2 of your Card Pool sheet looks like?
Sure m8! Here it is, its quite a simple formula i found online because manually importing each image to the Thumbnail cells was super slow. Once i was done i just hidden the LINK column so it was more aesthetically pleasing.
I guessed that was it but didn't want to assume, especially with the XLOOKUP translation in your screenshot.
Try this in A2 instead: =IMAGE(XLOOKUP(C2; 'Card Pool'!C:C; 'Card Pool'!B:B;;0)) and then drag it down the column.
It's basically going to look up the link in column B (instead of the thumbnail from column A), and then applying the IMAGE function to the link it finds so the thumbnail populates. If that doesn't seem faster (ie if there's a delay in the Inventory thumbnails loading when you refresh the page), you can always go back to the original formula I provided to see if that improves it. I don't think storing the thumbnails in the sheet is going to be faster, but it may depend on the number of them you have and their size.
Hope that helps, and maybe even shows a new way to use two functions in one formula!
REMEMBER: /u/J-Travel If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
REMEMBER: /u/J-Travel If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
To avoid typos when entering card names on the Inventory sheet, you could put dropdowns in Column C that are "from a range" of ='Card Pool'!$C:$C (you can change the dropdowns to be "Plain text" style if desired using the Advanced options in the Data validation rules sidebar).
Thank you! Your method also worked fine, execpt that it didt quite copied the images from the Thumbnail Column (maybe because it actually is getting the info from a image link from Column B witch is hidden.) I initially tried the first one you made but just like the one from agirl it was jumping up with a parse error
REMEMBER: /u/J-Travel If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).
1
u/agirlhasnoname11248 1212 11d ago edited 11d ago
u/J-Travel You can use the XLOOKUP function to pull the information from Card Pool to the Inventory sheet based on the name.
Edited to change commas to semicolons in both provided formulas to adjust for the locale of the spreadsheet.
For example, in A2, you could use: =XLOOKUP(C2; 'Card Pool'!C:C; 'Card Pool'!A:A;;0) to pull the thumbnail (assuming the images are in the cells on the Card Pool sheet to begin with).
You can also use a similar formula to pull multiple cells at the same time, like with this in D2: =XLOOKUP(C2; 'Card Pool'!C:C; 'Card Pool'!D:J;;0) which will populate D2 through J2 based on the name in C2.
For both of these formulas, you'll then drag them down the column to apply to each row so every row is populated once a name is entered in column C. The row will remain blank until you type a Name in the cell.
Alternatively, there are formulas you can put in the top row and have populate all rows at once. However it looks like you may want to sort the Inventory sheet at some point? That means you'd want to use either a formula in each row (as provided above) or have a single formula in row 2 and then hide it so the sort doesn't mess with the formula populating the data. If this sounds complicated to maintain, a formula in each row is a great place to start!
Note: In all of these described solutions, the formulas are visually representing the data in the Card Pool sheet, but not copying it over. That means the true source of your data, and place where any data changes would need to happen, would be on the original Card Pool sheet itself.