LokaPoka
New Member
- Joined
- Aug 30, 2012
- Messages
- 6
I have a spreadsheet that contains names in one column (C) and email addresses in another column (D). Each person/row is a part of one or more of six groups. I have captured their group information in columns G-L which are titled simply 1-6, and then if a person is part of group 1 they get a 1 in the 1 column in their row, and if they are part of groups 3 & 6 then they get a 3 and a 6 in the 3 and 6 columns in their row. There will be additional names added and possibly subtracted, and each person may want to either be added to or removed from a group.
For each of the groups I want to create a cell that combines all of the email addresses that belong to that group and have the email addresses separated by a comma, and I need this cell to automatically update so that it is always currently. Ideally this would not use VBA. And my dream is that it could be made into a hyperlink with the addresses for a particular group with me CC'd with a specific subject.
Here's what I've tried: I have concatenated the email addresses for a specific group, but that does not automatically update. I tried using the hyperlink/mailto function, but I think it may have a character limit because it kind of worked for a small list but not larger ones, plus it doesn't automatically update anyway. I tried using INDEX+MATCH but that only returns one value. It's like I need a reverse INDEX+MATCH, where I specify a value and it concatenates the result into one cell. I'm kind of at a loss but it seems like this is something Excel should be able to do. Also this spreadsheet will be used and updated by others which is why I need to stay away from VBA/macros if I can.
For each of the groups I want to create a cell that combines all of the email addresses that belong to that group and have the email addresses separated by a comma, and I need this cell to automatically update so that it is always currently. Ideally this would not use VBA. And my dream is that it could be made into a hyperlink with the addresses for a particular group with me CC'd with a specific subject.
Here's what I've tried: I have concatenated the email addresses for a specific group, but that does not automatically update. I tried using the hyperlink/mailto function, but I think it may have a character limit because it kind of worked for a small list but not larger ones, plus it doesn't automatically update anyway. I tried using INDEX+MATCH but that only returns one value. It's like I need a reverse INDEX+MATCH, where I specify a value and it concatenates the result into one cell. I'm kind of at a loss but it seems like this is something Excel should be able to do. Also this spreadsheet will be used and updated by others which is why I need to stay away from VBA/macros if I can.
