I have an Excel spreadsheet with multiple sheets. In the first spreadsheet, I have names listed in Column G.
In a different spreadsheet, I want to pull in all rows from the first spreadsheet that contain "Bob" in column G.
I have been trying to figure this out with =VLOOKUP and =FILTER but have not figured it out.
what you mean by 'pull'? copy somewhere else? i do not believe you can copy entire rows and combine it with lookup process for automation. you have to write code
If I understand you correctly (yes you can "pull" entire - or partial - rows from 1 sheet to another, without using code)r, you would need an ARRAY INDEX/SMALL/IF combo, but to be more specific, you will need to post a small sample WB with some dummy data and show expected outcome
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
See attached images for what I'm referencing. I want Column G to be what informs what gets copied to the other sheets. In other words, if "Mike" is listed in column G, I want the entire row to be copied to the "Mike" sheet. That will leave me with 11 rows of information within the "Mike" sheet.
Image1.PNG
Image2.PNG
Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
File is now attached
In "Mike"
in A2
=IFERROR(INDEX(Sheet1!$A$2:$G$22,AGGREGATE(15,6,(Sheet1!$G$2:$G$22="Mike")/(Sheet1!$G$2:$G$22="Mike")*(ROW($G$2:$G$22)-ROW($G$1)),ROWS($1:1)),COLUMN($A:A)),"")
Copy across and down
Change "Mike" for other sheets
Last edited by JohnTopley; 03-18-2021 at 03:47 AM.
Great, thank you for this!
When I apply it to A2 and drag it across to G and down the columns, I get 'Spill' errors in the cells. See attached. I do get what I'm looking for but the entries are listed after the spill errors. Any thoughts?
My typo - aplogies
=IFERROR(INDEX(Sheet1!$A$2:$G$22,AGGREGATE(15,6,(Sheet1!$G$2:$G$22="Mike")/(Sheet1!$G$2:$G$22="Mike")*(ROW($G$2:$G$22)-ROW($G$1)),ROWS($1:1)),COLUMNS($A:A)),"")
Thank you!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks