Populating a weekly schedule based on criterias with formula

[Rebelexcel]

Hi,

I have a task where I need to schedule the amount of people in the office present in the building at the same time.

The idea is to populate a weekly schedule based on 4 different criterias, which is chosen in a drop down list for each headcount.

A: Custom (added manually, so empty)
B: Mondays and Wednesdays, every second friday on even week numbers.
C: Tuesdays and Thursdays, every second friday on odd week numbers.
D: One week at the office (full week), next week at home.

It will be something like this:

CovidSchedule.JPG

My first idea was to do an IF statement, but I got lost in the complexity of so many different criterias. My second idea was to populate a scheme in another tab per week and criteria, and then do a vlookup but I didn't get it to work either.

Do any of you have an idea on how to solve this easily?

The goal is that each responsible team leader should fill in this schedule, and it will summarize in a number of people present at the office at the same time.

I've attached the test file that I've been using.

Appreciate any help, thanks a lot.

R

[Bo_Ry]

Please try Single Cell formula
G9

Please Login or Register  to view this content.

or G9:G14

Please Login or Register  to view this content.

[Rebelexcel]

Wow, that looks great, I'll give it a try.

There might be an issue with option A. If people choose A, they are supposed to fill in manually, which will override the formula, and if the person would then change their mind the formula would be broken. Is there any smart way to solve this?

[Bo_Ry]

Use G9

Please Login or Register  to view this content.

[Rebelexcel]

I'm sorry but i'm getting the same issue. If I choose A for person 6 and then add a bunch of X'es to mark their presence, hence removing the formula, and then changing person six back to B there are no formula so it still shows the manual entering.

As I'm typing this I'm realizing there is probably no good solution for this? Except a kind of "Reset" macro that fills everything with a formula again, but that is out of my reach.

[JeteMc]

You may want to send a message to one of the moderators/administrators (see Members List at top of page) requesting that the thread be moved to the Excel Programming / VBA / Macros forum.