software

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: www.excelforum.com/member.php...foamp;userid=9301
View this thread: 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: www.excelforum.com/member.php...oamp;userid=31996
View this thread: www.excelforum.com/showthread...hreadid=517788One play ..

In Sheet2, A1 will house, eg: ref1

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

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

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
savefile.com/projects/236895
xdemechanik
---
quot;okanemquot; gt; wrote in
message news
gt;
gt; I have a problem I know someone has an easy answer too.
gt;
gt; Sheet 1 contains the following
gt;
gt; A1 : A6 all have ref1 entered in them
gt; A7 : A12 all have ref2 entered in them
gt;
gt; B1 : B6 list ref1 unique id's 60001 to 60006
gt; B7 : B12 list ref2 unique id's 70001 to 70006
gt;
gt; On sheet 2 I want to be able to type in either ref1 or ref2 into Cell
gt; A1 making the entire list of unique id's relating to that ref appear in
gt; B1 to B whatever it takes to list them all.
gt;
gt; There are a lot of ref's and multiple unique ID's per ref.
gt;
gt; Tried using the vlookup but only the first unique ref relating to the
gt; ref I entered gets returned.
gt;
gt; Hope someone can help
gt;
gt; Eternally grateful
gt; Okanem
gt;
gt;
gt; --
gt; okanem
gt; ------------------------------------------------------------------------
gt; okanem's Profile:
www.excelforum.com/member.php...foamp;userid=9301
gt; View this thread: www.excelforum.com/showthread...hreadid=517788
gt;
gt; Select B1:C1, copy down to say, C20
gt; to cover the max expected extent of returns for the reference in A1

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