Select data based on cell value

[rovert]

Hi All,

I have an excel workbook with 2 sheets named "Info" and the other "Newcastle".

Info sheet consists of 100's of rows of data, see below:

[TABLE="width: 1038"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]PartNumber[/TD]
[TD]QTA[/TD]
[TD]B3_Free[/TD]
[TD]B3_Locn[/TD]
[TD]Branch[/TD]
[/TR]
[TR]
[TD]BC2020[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Birmingham_Branch[/TD]
[/TR]
[TR]
[TD]BC2051R[/TD]
[TD]1[/TD]
[TD]43[/TD]
[TD]201C2,53A[/TD]
[TD]Newcastle_Branch[/TD]
[/TR]
[TR]
[TD]BC2056[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Birmingham_Branch[/TD]
[/TR]
[TR]
[TD]BC2056R[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Birmingham_Branch[/TD]
[/TR]
[TR]
[TD]BC2063[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]17D[/TD]
[TD]Builds[/TD]
[/TR]
[TR]
[TD]BC2063R[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]17D[/TD]
[TD]Birmingham_Branch[/TD]
[/TR]
[TR]
[TD]BC2065[/TD]
[TD]1[/TD]
[TD]28[/TD]
[TD]201A2[/TD]
[TD]Newcastle_Branch[/TD]
[/TR]
[TR]
[TD]BC2065R[/TD]
[TD]1[/TD]
[TD]46[/TD]
[TD]13M5[/TD]
[TD]Newcastle_Branch[/TD]
[/TR]
</tbody>[/TABLE]

The Newcastle sheet consists of just headings, see below:

[TABLE="width: 445"]
<tbody>[TR]
[TD="width: 103"]PART[/TD]
[TD="width: 92"]ORDER QTY[/TD]
[TD="width: 111"]STK[/TD]
[TD="width: 139"]LOCN
[/TD]
[/TR]
</tbody>[/TABLE]


What i need to be able to do is list all data in the Newcastle sheet where branch = Newcastle_Branch in the Info sheet..

Thank you in advance

Rovert

 

[Special-K99]

This assumes the 4 columns on the Newcastle sheet are the first four columns on the Info sheet - unsure as they have different names and you havent specified what's what.

Try

in Newcastke!A2
=IFERROR(INDEX(Info!$A$2:$E$100000,AGGREGATE(15,6,ROW(Info!$E$2:$E$100000)/((Info!$E$2:$E$100000="Newcastle_Branch")),ROWS(A$2:A2))-(2-1),COLUMN()),"")
copy across and down for as many rows as you have on the Info sheet

 

[rovert]

This assumes the 4 columns on the Newcastle sheet are the first four columns on the Info sheet - unsure as they have different names and you havent specified what's what.

Try

in Newcastke!A2
=IFERROR(INDEX(Info!$A$2:$E$100000,AGGREGATE(15,6,ROW(Info!$E$2:$E$100000)/((Info!$E$2:$E$100000="Newcastle_Branch")),ROWS(A$2:A2))-(2-1),COLUMN()),"")
copy across and down for as many rows as you have on the Info sheet

Hi Special-K99

This is how both sheets will look like after.

[TABLE="width: 649"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD]Info sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ShaftecNumber[/TD]
[TD]QTA[/TD]
[TD]B3_Free[/TD]
[TD]B3_Locn[/TD]
[TD]Branch[/TD]
[/TR]
[TR]
[TD]BC2020[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Birmingham_Branch[/TD]
[/TR]
[TR]
[TD]BC2051R[/TD]
[TD]1[/TD]
[TD]43[/TD]
[TD]201C2,53A[/TD]
[TD]Newcastle_Branch[/TD]
[/TR]
[TR]
[TD]BC2056[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Birmingham_Branch[/TD]
[/TR]
[TR]
[TD]BC2056R[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Birmingham_Branch[/TD]
[/TR]
[TR]
[TD]BC2063[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]17D[/TD]
[TD]Builds[/TD]
[/TR]
[TR]
[TD]BC2063R[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]17D[/TD]
[TD]Birmingham_Branch[/TD]
[/TR]
[TR]
[TD]BC2065[/TD]
[TD]1[/TD]
[TD]28[/TD]
[TD]201A2[/TD]
[TD]Newcastle_Branch[/TD]
[/TR]
[TR]
[TD]BC2065R[/TD]
[TD]1[/TD]
[TD]46[/TD]
[TD]13M5[/TD]
[TD]Newcastle_Branch[/TD]
[/TR]
[TR]
[TD]BC20907[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]Newcastle_Branch[/TD]
[/TR]
[TR]
[TD]BC2093R[/TD]
[TD]14[/TD]
[TD]80[/TD]
[TD]201C1[/TD]
[TD]Newcastle_Branch[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Newcastle sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PART[/TD]
[TD]ORDER QTY[/TD]
[TD]STK[/TD]
[TD]LOCN[/TD]
[TD]PICKED[/TD]
[/TR]
[TR]
[TD]BC2051R[/TD]
[TD]1[/TD]
[TD]43[/TD]
[TD]201C2,53A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BC2065[/TD]
[TD]1[/TD]
[TD]28[/TD]
[TD]201A2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BC2065R[/TD]
[TD]1[/TD]
[TD]46[/TD]
[TD]13M5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BC20907[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BC2093R[/TD]
[TD]14[/TD]
[TD]80[/TD]
[TD]201C1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance..

Rovert

 

[Special-K99]

So did it work?