Speaker 1: Hello, and welcome to Excel Highway, your one-stop shop for all your Excel needs. In today's video, I want to share with you a resource planning template that I created in Excel. With this file, you can understand if you have enough employees to support your projects. If you have a certain department that you have an issue with, a certain person that you do not have enough work for them, etc., etc. It's a very simple yet effective template, and I will walk you through how to build it on your own. Now, as always, if you enjoyed my content, please hit that subscribe button so you will not miss my new videos. The file has two sheets, one called Schedule, which you are seeing right now, and one called Team. In the Team sheet is where you define the departments and the names of the people that work in that department. In my case, I'm taking departments from my world of software products. So we have back-end, front-end, quality database, and product departments. And here I have just random names that I created using my name generator file. If you're not aware of that, you can check it out here in the link above. It's very nice. You can generate random names very easily and quickly. Here on the columns, you can see I have months. So I have a monthly availability table for them. And I started off next year, January 23, and here I just use edate to jump one month ahead for the full year. And here I just generated random numbers. I have it over here. I generated a random number using RAND, and then I just VLOOKUP to generate fixed numbers between 0.25, 0.5, 0.75, and 1. I just wanted to have nice options, not just get any number between 0 and 1. So I used that and pasted that by value, so I got a nice distribution. Okay, you see every number here is between 0 to 100%. Most of them are 25%. So what this will do, this will populate the names. I'm using, you see, TRANSPOSE, SORT, and OFFSET. Let's break this down step by step. The OFFSET, if you're not familiar, it's a very useful formula to bring data from other locations or sheets or whatever. So OFFSET, it has, you see, five arguments. REFERENCE is where you're starting. So in this case, I'm starting over here in the first cell. Then it asks me how many rows I want to go. So imagine you're moving with a mouse. So if you press 1, it moves 1. If you press 2, it moves 2. Columns the same thing. I just left that open. HEIGHT is basically how many rows you want to add here. So I'm going to use COUNTA. Check how many rows I have here in the data. Minus 1 because I don't want the header. And WIDTH is 2, meaning I want two columns. And once I do that, I just get that list. You see? If I had put, let's say, 5, so it starts five rows below that. So that's the first step. That's the OFFSET. Now, let's keep that. Then there's the SORT. The reason I used SORT is because I wanted all the departments to be in the same area. You can see that I have database and database separated by quality. But here, using the SORT, they are seen together. That's what the SORT does. And the last thing is the TRANSPOSE, which just transposes, just like you do manually, takes it from this view to this view. So that's how I built this part and why it's very helpful because if now, if I want to add a backend, for example, I just add that here and immediately you see it's added over here. It just transfers all the manual input, so you need to make sure you update your table. So that's this part. This shows over here. There's a month, a selection month, where you can select a month and you see the information changes, the availability. That's a simple index match. First of all, the month selection, if you're not familiar with the dropdowns, click a cell, go to data, go to this icon, data validation. By default, on any value, select list, and then I just went ahead and selected this area to give me the full year. So whatever I change, if I change the date here to, let's say, July, start from July, then I start from July. Okay? I'll bring that back. So this is how you select the months, the available index match. Index match is a very helpful formula, or basically combination. The match will return the row or column fitting your search, and index will basically give you the connection between the two. So here I am looking for, the array is B1 through N15, so basically this area. This is my array. I'm looking for a match for this column, for the name, and then a match for the row. And then I'm just getting the combination between the name and the month. That's all it does. So in the match, you need to set up what is the value you're looking for, what is the array, so this is the first column, and zero means it's an exact match. So this is what's giving me the percentage for each person. Okay? That's, this part is pretty clear. Over here, this is all manual, A through C. You need to set up a project, in my example, I have two projects. One is called a new app, and one is to migrate the data. The five departments, and I just define manually how many people I need. And you can see that whenever the book, which is just a summary of what I have over here, when it's not enough, it gives me a nice yellow color. It's very easy to do with conditional formatting. Go to Home, Conditional Formatting, and you can see I have a rule. You can just add a new rule. Use a formula, and say if C, sorry, D11 is less than C11, one or zero, then I just have a format. I select. That's what I did. Very simple, but very effective. You can see it over here, okay? And then you just drag it down, and it works. The booked column is a summary of the column, and here you have the percentages that you key in manually. Now, what's also nice here that you see you have gray area, that indicates that this is a row for frontend, and these are backend people, so you should not schedule them here. You can, of course. It's not blocking you, which you can also do, by the way. You could also block it with Protect Cheat if you want, but that's a little bit more complicated because as things move, you need to constantly do that, but you can. What I did is just some conditional formatting again, and you can see the formula. I'm checking if B5, in this case, the name of the department needed, is not the same as the name of the department where this person works. If that is the case, then I'm just coloring the cell in gray, and that just gives you a nice view of what you can book, and of course, I added this separator between the projects, so if you have another project, for example, you can just take this, paste it over here, delete everything, and then you can just define, I don't know, a new project, call it this way, and you can say, I just need three quality people, sorry, and one and a half product people, and then you can just book whoever you want, and of course, immediately you see I have a problem with the personnel. Again, conditional formatting here for the row. It's just checking if there are limitations and you're above the limitation. Now, usually you can build that, and that should give you an indication if you can support all the projects, or do you need more people, or do you need to prioritize work, but you can also move within the month and see if you have a certain problem in a certain month, and of course, using this template, you can actually build this for more than one month. You can just create a copy and call this February, for example, call this January, and then you just have every month on its own where you don't have to change the date every time. I just like, actually, flexibility to be able to see the same sheet everything. So that's it. If you enjoyed this video, please subscribe, hit that like button, share, and leave a comment if you want to share your thoughts, I'd be happy to read them. Take care now.