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:
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:
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:
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:
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
Bookmarks