On Fri, 1 Aug 2008 16:35:01 -0700, fatdave
wrote:



"Lars-Åke Aspelin" wrote:

On Fri, 1 Aug 2008 10:45:01 -0700, fatdave
wrote:

I have got a list of people, First name, Surname, Tel Number and a small
note. Each of these people are members of groups. In columns E - G I can put
a Y or a 1, witch ever is easier. Each group has it own tab. What I would
like to happen is: - when I put someone on the mater list and add them to one
or more groups in that then will also be on the group tab, with out any extra
input from myself.

I have a list with

Column A Surname
Column B First Name
Column C Tel Number
Column D Note
Column E Group 1
Column F Group 2
Column G Group 3


Any ideas?


If your master table is in Sheet1, then you can try the following
formula for cell A2 in the sheet that should be filled with data for
people in Group 1 (which are indicated by your column E in Sheet 1).

Note: This is an array formula that has to be entered by
CTRL+SHIFT+ENTER rather than just ENTER.

=INDEX(Sheet1!A$1:A$10,1/LARGE(1/ROW(A$2:A$10)*(Sheet1!$E$2:$E$10<""),ROW()-1))

Copy this to cells B2, C2, and D2 to get the other data
Copy A2:D2 down as far as needed

Increase the 10 in the formula to cover all your rows in Sheet1

In the sheets for the other groups you can use the same formula, just
change E to F, G, etc

To populate the Group sheets everything you have to do is to put
anything, a "Y" or a "1" will do fine, in the group columns of Sheet1.

Hope this helps / Lars-Åke


I am sorry, but all I get is a #NAME? in the box that I put the formula in,
ps whitch box do I need to use the CTRL+SHIFT+ENTER rather than just ENTER.?

Are you sure that you get #NAME? and not #NUM! ?

You will get #NUM! if you don't enter the formula as an array formula.
To get #NAME? you must have misspelt some formula (like LAREG instead
of LARGE)

To enter the formula correctly, first select cell C2 then write the
entire formula in the formula field. Then you press the buttons CTRL
and SHIFT and hold them both down while pressing the ENTER button.
If this is done correctly Excel will put "curly brackets" around your
formula so it will löok like

{=INDEX(Sheet1!A$1:A$10,1/LARGE(1/ROW(A$2:A$10)*(Sheet1!$E$2:$E$10<""),ROW()-1))}

in the formula field. Those { } should not be typed by you.

Hope this helps / Lars-Åke