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)
Recommended posts
24 Responses to “Shift Schedule”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
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 [...]
Is there a way to do it in reverse where you look up the name and it gives you the days, without moving the data around? What part of the formula would change?
Zachary
Yes, it is possible.
Array formula in cell N19:
Copy cell N19 and paste to cells below.
Alright I see, I started to figure it out with switching it out to column instead of row. Although why have the $A$1:A1 at the end? I have it set to go A1, B1, etc. I do have a bit more complicated setup though as it is searching for a mark in a certain cell first then a different mark. So you have x's where I have C's and then T's and then M's. Issue I have is that with the Small formula it tells which number to look for so it doesn't repeat already said numbers but when it gets to the second letter it errors because it is already on the 6th number and not the first where there may not be a 6th number based on the value. Here is my Formula-
{=IFERROR(INDEX('Equipment & Bucket Fitment'!$A$2:$A$2000,SMALL(IF(INDEX('Equipment & Bucket Fitment'!$B$2:$LZ$2000,0,MATCH($A$3,'Equipment & Bucket Fitment'!$B$1:$LZ$1,0))="C",MATCH(ROW('Equipment & Bucket Fitment'!$A$2:$A$2000),ROW('Equipment & Bucket Fitment'!$A$2:$A$2000)),""),ROW('Equipment & Bucket Fitment'!A1))),IFERROR(CONCATENATE(INDEX('Equipment & Bucket Fitment'!$A$2:$A$2000,SMALL(IF(INDEX('Equipment & Bucket Fitment'!$B$2:$LZ$2000,0,MATCH($A$3,'Equipment & Bucket Fitment'!$B$1:$LZ$1,0))="T",MATCH(ROW('Equipment & Bucket Fitment'!$A$2:$A$2000),ROW('Equipment & Bucket Fitment'!$A$2:$A$2000)),""),ROW('Equipment & Bucket Fitment'!A1))),"T"),CONCATENATE(INDEX('Equipment & Bucket Fitment'!$A$2:$A$2000,SMALL(IF(INDEX('Equipment & Bucket Fitment'!$B$2:$LZ$2000,0,MATCH($A$3,'Equipment & Bucket Fitment'!$B$1:$LZ$1,0))="M",MATCH(ROW('Equipment & Bucket Fitment'!$A$2:$A$2000),ROW('Equipment & Bucket Fitment'!$A$2:$A$2000)),""),ROW('Equipment & Bucket Fitment'!A1))),"M")))}
Basically once it finds all the C's it then goes to find all the T's but errors because it is already on the 6th number because of the Small formula. Most times there isn't a 6th T let alone a 3rd, problem is I can't just choose a cell to change back to 1 because the cell varies based on the data referenced.
Zachary,
Although why have the ROWS($A$1:A1) at the end?
It won't break the formula if you insert columns or rows.
Issue I have is that with the Small formula it tells which number to look for so it doesn't repeat already said numbers but when it gets to the second letter it errors because it is already on the 6th number and not the first where there may not be a 6th number based on the value. Here is my Formula
It looks like you can simplify your formula to a great extent. Try using an IF function and use all your criteria in the first argument.
Isn't that what I am doing with IFERROR though? Not sure how I can simplify it. But the problem still stands with it erroring out when searching for the next letter. Even if I implemented an IF and simplified it, how would I get it to reset to 1 when its finished finding all the C's and start at 1 with T's?
Zachary
Even if I implemented an IF and simplified it, how would I get it to reset to 1 when its finished finding all the C's and start at 1 with T's?
If you could look for all C T and M in one logical expression would make it easier but I don't understand your formula.
This part seems to fetch values from a specific column? Why not look in the entire cell range 'Equipment & Bucket Fitment'!$B$2:$LZ$2000 ?
INDEX('Equipment & Bucket Fitment'!$B$2:$LZ$2000,0,MATCH($A$3,'Equipment & Bucket Fitment'!$B$1:$LZ$1,0))
I would like to look for all at one but can't seem to get it in where excel will accept it. Although I do need it to prioritize C's first, then T's, then M's. As for the part you referenced, the only thing that is specific to one spot is the $A$3, which is the cell we enter a value into. $B$2:$LZ$2000 is where all the C's, T's, and M's are. $B$1:$LZ$1 is a range across the top that has some of the data for lookup. Basically, the formula makes it so we can look up a piece of equipment and it shows the attachments that fit or need testing or need modification, the with a few changes it will also be able to look up an attachment and show which machines it fits on. but the issue comes at hand where when going into the second letter Small is already on 4th or 5th or 6th value. When I mean farther ahead, I mean I have 20 cells with this formula and as it goes down the cells the lookup number for the Small formula is going up.
Chart with Letters
https://postimg.org/image/hvl55g1gt/
Sheet with the Formula in It
https://postimg.org/image/b54nw2ywd/
It didn't post my last comment for some reason. Hmmm. well I will just give a quick explaination of the formula. It is almost just like yours but basically repeated a few times. The reason it is harder to lookup all three at the same time is I need to prioritze C's then T's and then M's. Specifically because the C's are more important. And I am using CONCATENATE for T's and M's and adding a letter at the end of the number so I need it to specify that. The iferror makes it so that if it can't find anymore C's then it moves to the formula that looks for T's and once it can't find anymore T's it moves to M's. It uses iferror to separate C's from the T's and M's and then a second iferror within that one to separate T's and M's. The point of the formula is for us to look up machines by their number and have it show the attachments that fit it.
Zachary
Thank you for describing your worksheet in great detail.
Do you really need to have all C M and T in one search formula?
See this example workbook: Zachary.xlsx
We thought about having them separate and if I split the current one up it works like that but we are really trying not to do that.
Zachary,
I understand. The formula in column V in the attached workbook below filters C's first and then M and T's in any order.
Zacharyv2.xlsx
I think I am getting closer.
So at this point I have three working formulas, one of which is mine and two of yours. One of mine and one of yours require the lookup of C, T, and M to be like this {"C","T","M"} and within those array brackets. It works one way but when I reverse it to be able to lookup the opposite value, it does not work when there are multiple letters within those array brackets and I do not know why. The last one is yours and it was the last one you sent me within the Zachary2 document. That one works great and actually works really good with the CONCATENATE formula for T and M, but the only issue is when I have it in 50 cells it runs my computer out of ram. I am running a powerful computer and still run low on ram and cpu trying to process it, it also takes about 10 seconds to process. Otherwise it is the best working one so far.
I did just notice that for some reason, here at work we have 32bit office installed. Maybe swapping to 64bit may help with the processing of the formula.
Zachary
I am happy my formula works great.
the only issue is when I have it in 50 cells it runs my computer out of ram. I am running a powerful computer and still run low on ram and cpu trying to process it, it also takes about 10 seconds to process.
Are there other cpu-intensive formulas in your workbook? Perhaps volatile functions? (Functions that recalculate every time you press F9, like TODAY(), INDIRECT())
https://www.decisionmodels.com/calcsecretsi.htm
There are other formulas that you can tell take a millisecond longer than others as well as they are a lot longer. But the thing is I can run 50 of them without issue and it only starts to become an issue when the one you gave me runs more than 5 of them. I don't use either of those formulas. I did once but not anymore. I use a lot of INDEX, IF, IFERROR, CONCATENATE, VLOOKUP, and MATCH. Even when I removed all other formulas and used just yours it worked up until past 10 of them which then it had to process.
Zachary
Even when I removed all other formulas and used just yours it worked up until past 10 of them which then it had to process.
I see, my array formula is the cause of the problem. I don't know how to make it faster unless a user defined function is an option.
At this point you have helped a great amount with getting the formula I need. At this point it should be tweaking a few things to either make yours process faster or use one the others and make them work correctly. I appreciate all the help you have given and will definitely recommend you to anyone needing help with excel that I can't help with. Thank you Oscar.
Zachary
You are welcome. Thank you for commenting.