Extract Data From List Dependant on cell criteria

[ExcelRoy]

Hi,

I am looking for a formula to extract data from a list on another sheet

Sheet1

D7 has a alpha numeric job reference

A13: Part Number
B13: Description
C13: Quantity
D13: Cost
E13: Order Date

I need this to show every entry in the main list on Sheet2 which can always change in terms of lines per job

Any help appreciated

 

[FDibbins]

I think you need to show some sample data, rather than just headings, along with your criteria, and some expected results.

You probably need to use something like the INDEX/SMALL/IF array formula

 

[Aladin Akyurek]

Care to post a small sample from the source data? Would you also clarify "D7 has a alpha numeric job reference"
since the headers you posted does not contain any reference to job numbers.

 

[ExcelRoy]

Hi, sorry, I will try and add some detail

My data on sheet 1

A13: Part Number
B13: Description
C13: Quantity
D13: Cost
E13: Order Date
F13: Job Reference (Alpha Numeric)

I would like sheet 2 to show each row of data from the job reference in D7

ie, Part Number, Description, Quantity, Cost, Order Date, from A1,B1,C1,D1 etc

Many thanks

 

[Aladin Akyurek]

Sheet2

D7 contains a job reference of interest.

In E7 just enter:

=COUNTIFS(Sheet1!F14:F400,D7)

Row 8 of Sheet2 houses the following headers:

A8: Idx, B8: Part Number, C8: Description, D8: Quantity, E8: Cost, and F8: Order Data.

In A9 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$9:A9)>$E$7,"",SMALL(IF(Sheet1!$F$14:$F$400=$D$7,ROW(Sheet1!$F$14:$F$400)-ROW(Sheet1!$F$14)+1),ROWS($A$9:A9)))

In B9 just enter, copy across, and down:

=IF($A9="","",INDEX(Sheet1!$A$14:$F$400,$A9,MATCH(B$8,Sheet1!$A$13:$F$13,0)))