Formula to pull rows of data based on a column cell value (Criteria)

Curious if it's possible to create a formula which pulls rows of data into another worksheet in the same workbook (Have formulas already in place in the desire worksheets). My intent is to auto update the worksheet when I add more data into the data source and avoid using a macro. Would like to organize based on Column "I" or PONumber. Attached is my test data. First worksheet is my datasource and the second worksheet is what I want pulled based on one (PONumber) criteria in column "I".

Any recommendation is welcome and if this is not possible or ideal please shut me down.

You can do this with 3 basic formulae. In the attached file I have set up a new sheet called Selected_records and put this formula in T2 of the DataSource sheet:

Formula:

Please Login or Register  to view this content.

This is copied down beyond your data (the hyphens show where it has been copied to), and this sets up a sequence of records which match the PO number chosen in the Selected_records sheet.

In the Selected_records sheet you can select the PO Number from the drop-down list in cell I1. This list is shown in column V - I extracted unique values using Advanced filter and then sorted the list. Then I put this formula in cell T4:

Formula:

Please Login or Register  to view this content.

which finds the row in the DataSource sheet where the first matching record appears - when this is copied down it will find the row of the second matching record, then the third, and so on.

In cell A4 I put this formula:

Formula:

Please Login or Register  to view this content.

which returns the data from column A for the record whose row is given in cell T4. This formula can be copied across, and it will return data from column B, then column C etc. I made a slight amendment to the formula in M4 to this:

Formula:

Please Login or Register  to view this content.

which will return a blank if there is no data in column M of the DataSource sheet (as most of the dates seem to be missing in the example file). The cell is formatted as a date, and then copied across to S4. Cell R4 has been formatted as General.

Then the formulae from cells A4:T4 can be copied down as far as you think you need them - I've copied down to row 30, as can be seen in column T. In your real file you will need to ensure that the formula in column T of the DataSource sheet is copied down beyond the amount of data that you have.

So, all you need to do is select a PO number in cell I1, and the display will change immediately to show that PO's records.

Hope this helps.

Pete