Project management sheet - "advanced" list problem

[Ztv]

Hi. Please see the attached file. I have this sheet where I have different projects. Then I have created a "Name_database" sheet. Each person can be allocated to a project, but only one project. When you have chosen one person, he/she is out of the list.

But one person can actually be allocated to more than one project - if the start date and end date does not overlap. But how do I implement that?

All suggestions are welcome.

[aydeegee]

Hi Ztv

I liked your neat way of dealing with not showing people who have already been allocated, but could not progress further on that route.

I have a few suggestions which I have strung together in the attached sheet. Does this meet your requirements?

Regards
Alastair

[Ztv]

Hi aydeegee - thanks for your contribution! I was just about to repost this problem in the VBA section of the forum since I thought it might needed some VBA to solve it.

I like that work around too. Though its not me but my colleague (an engineer) who made that. So I can't take all the credit .

I'll try to implement your suggestion in my work and return later today (or tomorrow)!

[Marcol]

Try this formula based approach.

I haven't tested its' robustness, so don't add rows before Row 1 for the time being

Fill the projects in chronological order.

[Ztv]

@Aydeegee: I have tested your solution. Its not a "simple" solution, but it seems like working perfectly .
@Marcol: I like your formula based approach better. However, if I change the dates of the projects (so they might not be in chronological order) it fails.

[Marcol]

Why would you want your projects in anything other than chronological order?

[Ztv]

I don't. But all the end-dates are expected end-dates and may change over time. So if I have a lot of projects in the sheet, I would have to change the order frequently.

[Marcol]

Okay that will involve VBa to update the cells as things change.

Once an entry is made in the table the formulae should change the validation lists, but it won't correct any entries that might have been selected and are no longer valid.

[Ztv]

Yes, I thought so... It's not that I don't like VBA - this problem just requires VBA skills that is out of my league .

So I can't say if aydeegee's solution is the optimal solution. But I can conclude that it seems to be working . Maybe I should move this thread to the VBA section? How can I do that?

[Marcol]

Start a new thread and post in the VBa forum, and include a link that refers to this one.

If you think this one was solved within the parameters of your original query, please mark it [SOLVED]