Value from list depending on conditions for Google sheets

[yomarcos]

Hi everyone,

I have been working on this for 2 days straight and I can't figure it out.

I have a list of employees. Depending on the dates and hours needed for every project I get True or False if there is a conflict. I want a formula that provides me with an alternative when there is a conflict. So in H2 the next option should be James. Thomas is in conflict but Daniel (next on the list) is also in conflict. Please help!!

 

[Marcelo Branco]

Hard to help without a data sample.

Including the dates as a criteria will probably make things much harder. Anyway, try to provide a data sample along with expected results.

M.

 

[yomarcos]

Hard to help without a data sample.

Including the dates as a criteria will probably make things much harder. Anyway, try to provide a data sample along with expected results.

M.

Example 3

Example Project,Team,Start Date,End Date,Hours/Day,Preferred,Conflict,Number of Conflicts per person per period of time,Available Hours/Day,Alternative A,Team2,1 Jan 2021,1 Jun 2021,3,Carl,2,2,,->,This is blank because there is no conflict A,,, B,Team2,1 Jan 2021,1 Jun 2021,3,Carl,2,2, B,,, C,Team2

docs.google.com

Thank you Marcelo. I put some comments on the file. I have only used Team2 to make it easier to read but it should work for both teams.

 

[Marcelo Branco]

As I was guessing, this new requirement makes the solution much more difficult. I'm afraid I won't be able to solve it.

M.

 

[yomarcos]

As I was guessing, this new requirement makes the solution much more difficult. I'm afraid I won't be able to solve it.

M.

Ok. Thank you for taking time to look at it