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:

PartNumber	QTA	B3_Free	B3_Locn	Branch
BC2020	2	0	0	Birmingham_Branch
BC2051R	1	43	201C2,53A	Newcastle_Branch
BC2056	1	0	0	Birmingham_Branch
BC2056R	1	0	0	Birmingham_Branch
BC2063	1	0	17D	Builds
BC2063R	1	0	17D	Birmingham_Branch
BC2065	1	28	201A2	Newcastle_Branch
BC2065R	1	46	13M5	Newcastle_Branch

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The Newcastle sheet consists of just headings, see below:

PART

ORDER QTY

STK

LOCN

<tbody>
</tbody>

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.

Info sheet
ShaftecNumber	QTA	B3_Free	B3_Locn	Branch
BC2020	2	0	0	Birmingham_Branch
BC2051R	1	43	201C2,53A	Newcastle_Branch
BC2056	1	0	0	Birmingham_Branch
BC2056R	1	0	0	Birmingham_Branch
BC2063	1	0	17D	Builds
BC2063R	1	0	17D	Birmingham_Branch
BC2065	1	28	201A2	Newcastle_Branch
BC2065R	1	46	13M5	Newcastle_Branch
BC20907	2	50	0	Newcastle_Branch
BC2093R	14	80	201C1	Newcastle_Branch
Newcastle sheet
PART	ORDER QTY	STK	LOCN	PICKED
BC2051R	1	43	201C2,53A
BC2065	1	28	201A2
BC2065R	1	46	13M5
BC20907	2	50	0
BC2093R	14	80	201C1

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

Thank you in advance..

Rovert

 

[Special-K99]

So did it work?