Look up I think

[fatdave]

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?

 

[Don Guillett]

Why a separate tab when you can just use ONE col with 1 2 or 3 and then
Data>autofilter>filter on the helper column for 2.

 

[Duke Carey]

The only way it happens "with out any extra input from myself." is with a
non-trivial macro. It'd watch for entries in any of those columns and copy
the row automatically. However, what would you do about duplicates, or rows
copied to the wrong group because of an input error?

Far better to have a single list with a single column to indicate group
membership. When you need to generate an up to date listing for a specific
group, use Microsoft Query (under Data->Get External Data->New database
query) to extract only the rows you want.

Nick Hodge has an excellent article about querying an Access database from
Excel. You can easily adapt it to querying an Excel database from Excel. Be
sure to read all the way to the bottom of the webpage


http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

 

[fatdave]

I need more that one column becouse thay can be in more than one group.

 

[pdberger]

OK -- why do you need them on separate tabs? Are you using this for data for
a Word mailmerge or something?

 

[fatdave]

This is so I can export eatch tab as a CSV file and inport it in to a sms list.

 

[Lars-Åke Aspelin]

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 A22 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

 

[fatdave]

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 A22 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.?

 

[Lars-Åke Aspelin]

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