Multi-Sheet Schedule Dispatcher & Formula Studio

Core Roster Logic: Master schedule feeds 7 Daily Views. Staff is segregated by Department. If an employee shift is scheduled as Free or blank, they are flagged as Invalid (Free) in the daily dispatch view.

Active Staff (Monday)

Invalid / Free Shifts

Total Departments

Target View

Monday View

Weekly Master Roster ('Weekly View')

Edit employee assignments and shifts. Daily departmental views update dynamically.

ID	Employee Name	Department	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Action

Daily View: Monday

Auto-routed from Weekly Master via formula logic. Filtered strictly by Department with Shift Validity check.

Google Sheets Formula Compiler

Dynamic multi-condition array formula to paste directly into your Google Sheets Daily tabs

=FILTER('Weekly View'!A$2:C$50, ('Weekly View'!B$2:B$50 = "Nursing") * ('Weekly View'!D$2:D$50 <> "Free") * ('Weekly View'!D$2:D$50 <> "Off"))

How it works: The =FILTER function takes range 'Weekly View'!A:C (Staff ID, Name, Dept). It checks both department matching * ('Weekly View'!B:B="Dept") and shift validity * ('Weekly View'!Col <> "Free"). Multiplying conditions acts as a vectorized Boolean AND.