Returning all values

[okanem]

I have a problem I know someone has an easy answer too.

Sheet 1 contains the following

A1 : A6 all have ref1 entered in them
A7 : A12 all have ref2 entered in them

B1 : B6 list ref1 unique id's 60001 to 60006
B7 : B12 list ref2 unique id's 70001 to 70006

On sheet 2 I want to be able to type in either ref1 or ref2 into Cell A1 making the entire list of unique id's relating to that ref appear in B1 to B whatever it takes to list them all.

There are a lot of ref's and multiple unique ID's per ref.

Tried using the vlookup but only the first unique ref relating to the ref I entered gets returned.

Hope someone can help

Eternally grateful
Okanem

[robert111]

first thought is, why not use a pivot table?

When you reply we will be better able to help

robert111

[Max]

One play ..

In Sheet2, A1 will house, eg: ref1

Put in B1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(Sheet1!B:B,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Put in C1:
=IF(Sheet1!A1="","",IF(Sheet1!A1=$A$1,ROW(),""))

Select B1:C1, copy down to say, C20
to cover the max expected extent of returns for the reference in A1

Col B returns the required results, all neatly bunched at the top
(Hide away col C, if desired)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"okanem" <[email protected]> wrote in
message news:[email protected]...
>
> I have a problem I know someone has an easy answer too.
>
> Sheet 1 contains the following
>
> A1 : A6 all have ref1 entered in them
> A7 : A12 all have ref2 entered in them
>
> B1 : B6 list ref1 unique id's 60001 to 60006
> B7 : B12 list ref2 unique id's 70001 to 70006
>
> On sheet 2 I want to be able to type in either ref1 or ref2 into Cell
> A1 making the entire list of unique id's relating to that ref appear in
> B1 to B whatever it takes to list them all.
>
> There are a lot of ref's and multiple unique ID's per ref.
>
> Tried using the vlookup but only the first unique ref relating to the
> ref I entered gets returned.
>
> Hope someone can help
>
> Eternally grateful
> Okanem
>
>
> --
> okanem
> ------------------------------------------------------------------------
> okanem's Profile:
http://www.excelforum.com/member.php...fo&userid=9301
> View this thread: http://www.excelforum.com/showthread...hreadid=517788
>

[Max]

> Select B1:C1, copy down to say, C20
> to cover the max expected extent of returns for the reference in A1

The 2nd line above should read as:
> to cover the max expected extent of returns for any reference entered in
A1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---