Question about data validation and e-mail-addresses

[Gygrid]

Hello guys!

I have to make a huge list of e-mail-addresses which have to be contacted by different people over a span of a couple of years. Therefore I have designed an excel-document with a sheet-tab for each person. This person will once a year have to contact x e-mail-addresses. I would like to keep track of which e-mail-addresses have been used and therefore should not be contacted.

Would it be possible to make a data-sheet for "all e-mail-addresses" and one for "used e-mail-addresses", make a data-validation drop-down menu in the sections for each agent who has to contact an e-mail, have them choose a random e-mail from the drop-down, and in some kind of way make an automated process where the mail they choose gets transferred from the "all e-mails" to "used e-mail-addresses" so that it is impossible to choose the same e-mail twice?

I hope this is understandable, or else I will be happy to elaborate.

Cheers,
Martin

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

If it were me, I would try to stick to one worksheet with the email addresses in it. Then I would add columns for dates.

When an address is contacted the date of last contact needs to be updated.

From that information you can determine who should be contacted soon (e.g. 11 months since last contact) who has never been contacted (date missing) who is over due (date over a year old).

 

[Gygrid]

Hi,
thanks for your answer RickXL!

That seems straightforward - sadly not as automated as I would like, but I guess this is the best way to do it!

Thanks for your help

 

[RickXL]

It may be possible to add some automation but you need to think through the process. For instance, your idea of moving the addresses to another sheet would work for the first year but what about the second year? The date would already be in place.

I know it is tempting to move data about in Excel but often the solution is easier if you try and think like a database designer. Databases seldom move data. They just use dates and status indicators then report on the data as if they had moved.

The other problem with Excel is that it is not the best thing for multiple simultaneous users.

 

[Gygrid]

Hi Rick,

I'll give it a shot! Thank you so much for your help