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

first thought is, why not use a pivot table?

When you reply we will be better able to help

robert111


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=517788

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" wrote in
message ...

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

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