00:00in my last video i showcased excel for inventory management now someone asked if i can use the same
00:05approach for roster planning well in my demo spreadsheet here the first section this one here
00:11staff roster planner here is going to be in one workbook and my master database it potentially
00:17could be on the second workbook here and basically i'm going to apply a filter and they give you the
00:22list based on a separate workbook altogether the first step i'll take will be to get the unique
00:28list of shift names from the master database using a function called unique unique and then the
00:35argument would be the entire shift name here like this close parentheses and hit enter and you can
00:40see that they are not in sorted order so basically in order to get a sorted order you could encapsulate
00:46the unit function with the sort function like that and you get the sorted values now you're going to
00:53associate this list on the drop down menu here put your cursor on cell d7 then you go to data
01:00on your
01:00ribbon data validation data validation over here on this data validation pop-up make sure you're on a
01:07settings tab and the allow you're going to select as lists because it's going to be a drop down list
01:11and you're going to click on this arrow here and then basically highlight the unique list that you have
01:17just created and hit enter twice like this one two and now you have a pull down menu on cell
01:24d7
01:24now to display the filtered list on whatever you have selected as filter in here put your cursor on
01:31cell b10 and say equal filter the first argument on the filter will be the entire master database itself
01:38like this and then the second argument is your condition for your filter so it's going to be
01:44uh your column on shift which is column h for me the entire column like this and basically you're
01:50going to say equal to whatever the user have selected on these seven and then it close parentheses and hit
01:57enter and you can see that on shift s3 all these are the names or the employees now if you
02:04change this to
02:06shift s2 you can see that all people who are in shift 2 s2 will be listed here likewise s1
02:13and s4 for shift
Comments