Complex If/Then assistance

[Robert_Ennaco]

Situation:

As with many offices we are operating under a half on/half off rotation and I am trying to build a spreadsheet that can show who is teleworking and who is not on a given week based on the team they are on.

Desired result:

A drop down menu that I can select which team should be teleworking. Once selected, personnel from that team listed in rows will have a column auto-populated with 'teleworking'. Additionally, personnel from the opposite team will have that column's text removed from the previous week.

Thank you in advance.

[#2]

@Robert_Ennaco 

Here's a solution for part of that, a drop down box that goes through a list of names and team assignments and, based on selection of team A or B, lists the names associated with that team.

This does use the functions FILTER and SORT, which are only available to those who have the newest version of Excel, so if it doesn't work for you, that would be why. Come back and let us know if that's the case, so another solution can be created.

I didn't incorporate dates and other features because it wasn't clear how exactly you were wanting that to operate. I was tackling only the basic problem of getting a drop down to select a team and have the members listed. The rest of that is refinement for which I'd ask a more complete description.

[#3]

a sample of how your sheet is set up might help but if you have people listed then have a column for "Team" then in the corresponding weeks you can do a check that looks at the week and 'calculates' if they are teleworking. if you have date field you can use something like
=IF(XOR(ISEVEN(WEEKNUM(C$2)),$B3="RED"),"teleworking","in office")
where row 2 are your dates and then column B is your Team designator (e.g. "BLUE" or "RED").