Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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 -- Max Singapore --- "Sally" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|