# Select data based on cell value

#### rovert

##### New Member
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..

Rovert

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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

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>

Rovert

So did it work?

Replies
0
Views
86
Replies
5
Views
204
Replies
0
Views
240
Replies
0
Views
363
Replies
2
Views
221

1,203,462
Messages
6,055,563
Members
444,799
Latest member
CraigCrowhurst

### 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.

### Which adblocker are you using?

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

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