Firing Pete Best

[BloodyBill]

I can't seem to figure out how to do this. (I use Excel 365.)
I have a master schedule sheet (below) of employees, their shifts, breaks and what fraction of the hour they work on what days.
I also have one sheet for every date of the month. That means 28-31 different dated sheets in the monthly workbook.

On each of the dated sheets, I use the filter function to populate the schedule for that day. On Monday, August 28, the Monday lines from the master schedule sheet get pulled into that individual date sheet. The sheet for Tuesday, August 29 pulls in the lines with Tuesday in the far-left column. And so forth.
All that works great.

Here's the issue: changes

Let's say halfway through the month, we fire Pete Best and add a new guy named Ringo. (Trust me, it's for the best.) I want Pete's data to remain on the dated worksheets before he was fired, but not appear on the dated worksheets after he was fired. If need be, I can insert empty lines in the master with his name. Likewise, I don't want Ringo to show up on the worksheets representing the first half of the month, when he wasn't employed.

Also, as of the 22nd of the month, Paul is switching to the night shift. I'll need to add a new night shift line on the Master Schedule sheet for Paul on each day, but I want his day shift stats to remain on daily sheets 1-22, but henceforth switch to the night shift. So, in essence, I want "The old Paul Monday day shift line if the date we're pulling to is on or before the 21st. If the date we're pulling to is on or before the 22nd, I want this new night shift Paul Monday line to pull in.

I've thought some about a column(s) that reflects the range of dates that this line on the master is in effect. For instance, the two Paul lines for Monday might have date ranges of 1/1/1961 to 8/21/2023 and 8/22/2023 to 12/31/2999. Then I get stuck.

I'd love a solution that modifies the filter function that is otherwise working well, and doesn't involve VBA, which all goes over my head.

Thanks!!

Day of week	Notes	Team Name	Shift	Phone Ext.	1st Break	2nd Break	8	9	10	11	12	1	2	3	4	5	6	7	8P
Monday		John Lennon	8:00 - 4:00	1234	12:30 PM	3:15 PM	1	1	1	1	0.75	1	1	0.75
Monday		Paul McCartney	8:00 - 12:00	5678	10:00 AM	N/A	1	1	0.75	1
Monday		George Harrison	Day Off	9632
Monday		Pete Best	8:00 - 1:00	7412	11:30 AM	N/A	1	1	1	0.75	1
Tuesday		John Lennon	8:00 - 4:00	1234	12:30 PM	3:15 PM	1	1	0.75	1	0.5	1	0.75	1	0.5
Tuesday		Paul McCartney	8:00 - 12:00	5678	10:00 AM	N/A	1	1	1	0.5	1	1	1	1	1	0.5
Tuesday		George Harrison	Day Off	9632				1	1	1	0.75	1	1	1
Tuesday		Pete Best	8:00 - 1:00	7412	11:30 AM	N/A	1	1	1	0.75	1	1	0.5

 

[Jeffrey Mahoney]

The Excel function FILTER can have multiple Criteria if you do it right. In the include section of the function you can create XOR criteria as long as the result returns TRUE or FALSE
=FILTER($K$14:$K$418,($K$14:$K$418="Pete Best")*($L$14:$L$418="Work"),"")
In this example I multiplied the Array of True or False Results to get a smaller filter. This is an AND result where both have to be true to be returned. If you add them together then you it would return an OR result.

In your case, you're going to need to add a column for each person (unless you have one already) that tests their WORK vs OFF. I'm sure that you have some rule that keeps their vacation and PTO. You could add a new criteria that for date ranges that indicate their employment status. Overall, the column would result in WORK or OFF so you can use it in the criteria.

I don't have your database to look at it's difficult right now other than to offer generic solutions.

Jeff

 

[BloodyBill]

Thanks, Jeffery!