Pulling List of Rows from other sheet dependent on matching Cell Value? HELP See Pictures!

apurcell

New Member
Joined
Jan 14, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have a workbook with Sheets 1 and 2 with data on them. This template is on Sheet 3. The template uses the cell D5 to pull data from these different sheets to populate. You type a project number (Proj#) in D5 and the rest should populate.
1587496082410.png


On "Sheet 1", I have a listing of all the different projects by Proj#. This sheet gives us the data for the Responsible Person, Exp Date, Department, Approved, Project title, and this data is populated using VLOOKUPS as there is only one result per project number. So for example, with project number 238, there is only one expiration date (6/30/2020). Got this working.

On "Sheet 2", I have a listing of Vendor Invoices, Description, PMT Date, % Comp, Proj#, and Amount. On this sheet, there can be more than one invoice per project number.
1587497003155.png

What I am trying to do: When I put a project number in cell D5 of my template on Sheet 3, I want to pull a list of all rows from columns J to P in "Sheet 2" with a matching project number in column O.

So if I put 302 in cell D5 in my Template, Rows 125, 126, & 128 Columns J-P from "Sheet 2" would fill in my template as follows:
1587497303218.png
 

Attachments

  • 1587495902998.png
    1587495902998.png
    28.9 KB · Views: 2
  • 1587495935420.png
    1587495935420.png
    37.5 KB · Views: 2

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You are using xl365 so you should be able to use the FILTER function.
VBA Code:
=FILTER(Sheet2!A2:G19,Sheet2!F2:F19=D5)
I can't use that funtion on my old xl2013 version, but I believe the formula goes in the rop left cell of the range you want to populate. You can test it bebore applying it to see what it does. the syntax is
Filter(Range array of data,Range to Match=Criteria Range)
 

apurcell

New Member
Joined
Jan 14, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hell
You are using xl365 so you should be able to use the FILTER function.
VBA Code:
=FILTER(Sheet2!A2:G19,Sheet2!F2:F19=D5)
I can't use that funtion on my old xl2013 version, but I believe the formula goes in the rop left cell of the range you want to populate. You can test it bebore applying it to see what it does. the syntax is
Filter(Range array of data,Range to Match=Criteria Range)
Hello,
Thank you for your response. It does not look like I have =FILTER() It just gives me =FILTERXML()
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
OK, you must have the earlier version of 365 then. If you do a web search for 'Filtrer Function Excel' and lodate the MS Support article, it gives the details. All of the versions starting with 2019 now have it. I use mostly vba so I don't really need it and have not bothered to upgrade to more recent version since I am retired and do not need to keep up for work purposes. Maybe if my system crashes again as it has in the past I will upgrade, but for now I just creep along with my old 2013 version.

Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,127,422
Messages
5,624,697
Members
416,042
Latest member
Oden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top