Excel help needed

[Vinod Lewis]

I have 2 columns A and B containing these values in sheet 1.

Teamleader Teammembers
John A
Derick B
John C
Derick D

In sheet 2 i will be just entering the team leaders name and I want a
formula in only in 1 cell that would pull up the corresponding team members
names to appear vertically.

ie: If I enter John in sheet2 Cell A1 then B1 and B2 should autopopulate
with value "A" & "C"

Any help is appreicated, thanks in advance.

 

[Max]

One easy play which might appeal to you ..

Assume source table in Sheet1's cols A & B, data from row2 down

In Sheet2,
Input for the teamleader is in A2, eg: John

In B2:
=IF(Sheet1!A2="","",IF(Sheet1!A2=$A$2,ROW(),""))
Leave B1 blank

In C2:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(Sheet1!B:B,SMALL(B:B,ROWS($1:1))))
Copy B2:C2 down to cover the max expected extent of source data in Sheet1.
Minimize col B. Col C will return the desired results for the input in A2, ie
the listing of team members, with all lines neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik

 

[Max]

Here's a working sample for the easy play suggested earlier:
http://freefilehosting.net/download/3l28k
Filter over in other sht.xls
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---

 

[Vinod Lewis]

Thank you max, this what i wanted. If you could explain the formula it would
be a great help.

Thanks once again.

 

[Max]

Thank you max, this what i wanted.

Welcome. Pl click the Yes buttons (like the ones below), won't you?

If you could explain the formula it would be a great help

Here's some explanation ..

In Sheet2,

Col B is the criteria col. It flags source lines which satisfy the criteria,
ie where the team leader is equal to the name input in A2, with arbitrary row
numbers via ROW(). These arb numbers will then be read by the "float-up"
formulas in col C.

In Col C
SMALL(B:B,ROWS($1:1)) returns the smallest number in col B in the top cell
in C2, the 2nd smallest in C3, and so on. ROWS($1:1) is the incrementer term
used in copying down, it simply returns the numbers: 1,2,3 ... in successive
cells as we copy down.

The returns from SMALL are passed to: INDEX(Sheet1!B:B, ..)
which then retrieves the corresponding names from Sheet1!B:B

The front IF check: IF(ROWS($1:1)>COUNT(B:B),"", ...)
ensures that neat looking blanks: "" are returned (instead of ugly #NUM!s)
once all the arb row numbers are exhausted in col C. COUNT(B:B) provides the
threshold here, it returns the count of the number of arb row numbers in col
B.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---

 

[Vinod Lewis]

Thank you very much, that explains the whole formula.

 

[Max]

Glad it helped. Do take a moment to click the Yes button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik