#REF error when deleting values from a row of concatenated cells.

So here is the problem.

I have a distribution matrix at work that we use to get email addresses for people who want to receive different types of documents. Emails are listed along the top and doc types are listed along the left side. Where the row and column intersect there is an "E" to indicate that the person would like that type of document. There is a formula next to each doc type that concatenates all of the email addresses that have a corresponding "E" on that row. The problem I am having is that when I delete a column, I revieve the #REF error. I am not the only person using and updating the information in this spreadsheet and can therefore not guarantee that people will not delete columns. When a person is removed from the project, one of the other people will go in and delete the column.

About a year ago we had a Matrix with a macro that would automatically fix this.

This is the code for that particular macro

Please Login or Register  to view this content.

I would like to modify it for use in the attached example.

Thank you so much for the help. The formula is exactly what I have been looking for. I am also testing the macro out in a copy of the matrix. The only thing I have noticed that will prevent me from using it is that it does not replace the info in the email column every time it is run, it just adds to it. Is there a way to have it clear the column before it puts the new info into it? Thank you again for the help, you have no idea how long I have been working on this.

This is even better. You are a rock star. Thank you so very much for all the help. This thing has been a pain in the neck to deal with in the past and now I do not see us having any further problems updating it. You are a life saver.