Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up I think
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up I think
Why a separate tab when you can just use ONE col with 1 2 or 3 and then Dataautofilterfilter on the helper column for 2. -- Don Guillett Microsoft MVP Excel SalesAid Software "fatdave" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up I think
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/datam...taexamples.htm "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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up I think
I need more that one column becouse thay can be in more than one group.
"Don Guillett" wrote: Why a separate tab when you can just use ONE col with 1 2 or 3 and then Dataautofilterfilter on the helper column for 2. -- Don Guillett Microsoft MVP Excel SalesAid Software "fatdave" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up I think
This is so I can export eatch tab as a CSV file and inport it in to a sms list.
"pdberger" wrote: OK -- why do you need them on separate tabs? Are you using this for data for a Word mailmerge or something? "fatdave" wrote: I need more that one column becouse thay can be in more than one group. "Don Guillett" wrote: Why a separate tab when you can just use ONE col with 1 2 or 3 and then Dataautofilterfilter on the helper column for 2. -- Don Guillett Microsoft MVP Excel SalesAid Software "fatdave" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up I think
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up I think
"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.? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Look up I think
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|