Extract Data From List Dependant on cell criteria

ExcelRoy

Well-known Member
Joined
Oct 2, 2006
Messages
2,465
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Oct 2, 2006
Messages
2,465
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)))
 

Forum statistics

Threads
1,148,291
Messages
5,745,905
Members
423,983
Latest member
blackworx

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