# Formula

S

#### Sally

I have associated several names with a group number in one spreadsheet (i.e.
one column has the name, the next column has the group number). I want to
move those names to a different spreadsheet and associate those names to a
specific column. Formula might be something like "if group number equals "1"
in spreadsheet X, list it in column Y in spreadsheet Z. Can anyone help?

Sally

M

#### Max

A simple way to formulas model it up dynamically ....
Assume your source data in sheet: x, cols A and B, data from row2 down,
where col A = names, col B = group numbers assigned
In sheet: z
Let's say the group number will be input in Y1, eg: 1
Put in X2:
=IF(Y\$1="","",IF(x!B2=Y\$1,ROW(),""))
Put in Y2:
=IF(ROWS(\$1:1)>COUNT(X:X),"",INDEX(x!A:A,SMALL(X:X,ROWS(\$1:1))))
Copy X2:Y2 down to cover the max expected extent of source data. Hide away
col X. The required list of names for the group num input in Y1 ie: 1, will
appear neatly packed at the top. Change the input in Y1 to say: 2, and you'd
get all the names for group num 2. voila? celebrato, hit the YES below