00:00This by far is the simplest way of making rota lists in Excel.
00:05Let me show you how this is done.
00:07Let's say, for example, the top portion of this spreadsheet here is something that you can distribute to all your
00:12employees.
00:12And the bottom portion could be located anywhere, maybe in a different workbook altogether.
00:17So this is how you start.
00:19Over here in cell B3, you're going to start off with a filter function.
00:23The first argument will be the shift name itself, all the way down.
00:27And then you're going to press F4 once to make both the row and the column fix, like this.
00:34And the second argument is your condition for your filter function.
00:38You're going to press open parenthesis and basically highlight Olivia's section here, like this.
00:44Right now, I don't have anything in here.
00:46I'm going to fill this up with a little x.
00:49You can use the checkbox if you like.
00:50But for simplicity, I'm going to use a simple x to indicate that Olivia is assigned a particular shift.
00:57And basically press F4 twice, like this, to make the column fix, but the row variable.
01:04And then you're going to say equal to little x.
01:08Like I said, I'm going to use little x to indicate the shift that's assigned for Olivia.
01:11And close parenthesis, like this.
01:13And the third argument is a value to display when the filter criteria is not met.
01:19I'm going to close parenthesis to close the filter.
01:22But you'll notice that as you apply Olivia a couple of shifts, maybe I'm going to apply Olivia shift 3,
01:29maybe shift 6, and maybe shift 9 and 10.
01:32You can see Olivia shift starts to go down this way.
01:36And it looks like it's overlapping with Liam, Sam, and Noah.
01:40To turn this table right side up, you're basically going to encapsulate the filter function with a transpose function like
01:47this.
01:48And then the whole table will shift horizontal.
01:50Now if you assign maybe shift 4 to Olivia like this, you can see shift 4 get added on Olivia's
01:57row.
01:57Now you can apply the same formula to the rest of the row, but you'll notice that anything that you
02:03assign to Olivia get updated to everyone else.
02:06But let's say for example in Sam, you assign him shift 8, it does not really work.
02:11The reason behind that is because the condition still pertains to Olivia.
02:16So basically what you're going to do is you're going to select that particular formula, press F2, and move this
02:22red bar or red box, which is the second argument on the filter function, to Liam and hit enter.
02:29And then you're going to do the same thing for Sam.
02:32Put your cursor here, press F2, and move the second argument to Sam.
02:38And I'm going to do the same thing with the rest of the employee.
02:49And now it's completed.
02:51Now just as a test, you can see that Sam is assigned shift 8.
02:54I'm going to assign Sam shift 5 with a small little x.
02:58You can see Sam's row time list get updated.
03:00Now to do a quick test, I'm going to apply a couple of shifts to everyone like this.
03:05And you can see that it's working perfectly fine.
03:10God bless you.
Comments