The first point to understand is that a formula can affect only the value of
the cell in which it resides. It cannot cause an action such as copying a
range of cells to another range. If you want a solution using formulas, the
formulas must be placed in your target worksheets. Of necessity, some
formulas will be complex.
It's not essential but it simplifies the formulas if you define a name for
the categories column of the master list worksheet. To define the name,
select the range in column A on your master list worksheet containing the
category names, plus several extra cells to allow for future growth. Then
from the Excel menu choose Insert, then Name, then Define. Type in
"Category" (without quotes) in
the top name box, click Add, then OK. If necessary, you can redefine the
name to refer to a larger range in the future, without requiring any change
to formulas. (With the formulas used here, you cannot define the name to
refer to the whole column.)
Now to construct your worksheet for your "family" contacts, select a blank
worksheet and enter "family" (without quotes) in A1. In cell A2, enter the
following array formula (must be entered with CTRL-SHIFT-ENTER rather than
ENTER):
=IF(COUNTIF(Category,$A$1)<ROW(1:1),"",SMALL(IF(Category=$A$1,ROW(Category)),ROW(1:1)))
The formula returns the row number of the first occurrence of "family" in
the category column in the master worksheet. In cell B2, enter:
=IF($A2="","",INDEX(Sheet1!B:B,$A2))
Extend this formula to the right to include as many columns as you use for
each contact. Now select all cells from A2 to this last cell, and use the
fill handle to extend downward all formulas until you start seeing blank rows
being produced. Continue extending downward several lines to allow room for
future growth. At this point you'll see all contacts in the "family"
category. The list will expand as new family contacts are entered in the
master worksheet (or reduce if a family contact is deleted). The entries in
column A are the row numbers of the family contacts in the master worksheet.
Now type in another category name in A1, and you'll see the contacts for
that category appear. This single sheet might be sufficient for your needs.
You could use Excel's provisions for data validation to attach a drop down
box to cell A1, containing your category names as valid choices.. (If
necessary, read your HELP information on data validation to see how to do
this).
If instead you want a separate sheet for each category, just copy the sheet
you've produced to new sheets and change the A1 entries to your various
category names.
