Shift Schedule
Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of dates and the y is names. The table values indiciate whether or not the employee is scheduled to work (i.e., filled or not). Is there anyway to pull a list of names in a canned daily report based on the date and whether the cell is filled? In other words, I want to lookup the y-axis headers as opposed to the cross reference value.
Thanks, Geoff
Answer
Formula in cell B16:
Array formula in cell B19:
How to create an array formula
- Select cell B19
- Paste array formula in formula bar
- Press and hold CTRL + SHIFT
- Press Enter
How to copy array formula
- Select cell B19
- Copy (Ctrl + c)
- Select cell range B20:B25
- Paste (Ctrl + v)
Download excel *.xlsx file
Shift schedule - Conditional formatting
Conditional formatting - Cell range B2:AF14
- Select cell range B2:AF14
- Go to tab "Home"
- Click "Conditional formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Enter this formula:=B$1=$B$16
- Click "Format.." button
- Go to tab "Fill"
- Pick a color
- Click OK
- Click OK
Conditional formatting - Cell range A2:A14
- Select cell range A2:A14
- Go to tab "Home"
- Click "Conditional formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Enter this formula:=INDEX($B$2:$AF$14, ROW(A2)-1, MATCH($B$16, $B$1:$AF$1, 0))="x"
- Click "Format.." button
- Go to tab "Fill"
- Pick a color
- Click OK
- Click OK
Recommended blog posts
Quickly highlight records in a list using multiple criteria in excel
Quickly highlight records in a list in excel (AND logic)
Quickly highlight records containing text strings in excel (AND Logic)
Category: Conditional formatting
Counting conditionally formatted cells (vba)
UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA
Highlight dates within a date range using conditional formatting
Question: How do I highlight dates that meet a criteria using conditional formatting? Table of contents Highlight values in a […]Comments(15) Filed in category: Conditional formatting, Excel
Highlight closest value in excel
This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]Comments(10) Filed in category: Conditional formatting, Excel
Category: Cross reference table
Looking up data in a cross reference table
Question: How would I go about looking up data in an cross reference table. I have the header row (i.e. […]Comments(19) Filed in category: Cross reference table, Excel
Reverse two-way lookup in a cross reference table
The table of contents below lets you quickly navigate to the formula you are looking for. The excel 2016 formula […]Comments(4) Filed in category: Cross reference table, Excel
Category: Schedule
Populate cells dynamically in a weekly schedule in excel
Table of Contents Populate cells dynamically in a weekly schedule Populate multiple cell values in a single cell in a […]Comments(33) Filed in category: Excel, Schedule
Highlight specific time ranges in a weekly schedule in excel
In a previous post I created a simple weekly schedule with dynamic dates. In this post I am going to […]Comments(20) Filed in category: Excel, Schedule
Schedule recurring events in a weekly schedule in excel
In our sequel about weekly schedule it is now time to create a recurring events. This is what we have […]Comments(12) Filed in category: Excel, Schedule
Category: Templates
Comments(22) Filed in category: Calendar, Excel, Templates
Comments(16) Filed in category: Calendar, Excel, Templates
Visualize date ranges in a calendar part 2
I have created a new version of Visualize date ranges in a calendar. This excel file let´s you enter names […]Comments(14) Filed in category: Calendar, Excel, Templates
- Download free PDF Excel guide
- Weekly new posts
- Excel tips and tricks
- Step by step tutorials
- VBA macros & custom functions
- Charts & Graphs
5 Responses to “Shift Schedule”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
Oscar you are genius! Thank you!
Geoff,
I am happy you like it!
This was tremendously helpful to me, but is there a way to then reference what other days in the month the resulting names are also scheduled?
Keeping with our example, if Robert, James, and Brian are all scheduled on the 14th, is there a formula to also tell me that at least one of these three will be working on all of the following dates: 1, 3, 4, 7, 8, 9, etc.?
Corrine,
great question!
See attached file:
Shift-scheduling-Corrine.xlsx
[...] Shift Schedule [...]