• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic data validation based on a gantt chart?

M

mridd

New Member
Hello all!


First and foremost--thanks for taking the time to read this and possibly contributing your genius. All help is hugely appreciated!


Here is my problem:


I am creating a workbook to help with creating the schedule at my job. I would love help getting these three pieces to work together with minimal hard-coding.


"Availability": The list of employees starting in A3, then the hours 8 am through 1 am repeatedly sequenced with headings for the days of the week. It's basically just a Gantt chart for availability. Currently, when any letter is put in a box that corresponds to the employee's unavailable hour, the box turns black--it's just conditional formatting.


"Shift list": This is just a list of the shifts that employees may be scheduled for, ie:

8am-4 pm

10am-6pm

3pm-9pm

5pm-1am


"The schedule": The list of employees is in column A, and Sunday through Saturday is across the top.


Currently, the manager has to go back and forth between the Availability chart, the shift list, and the schedule to determine what shifts each employee can work each day, and then type it into the cell himself.


I would like to put drop-downs in the Schedule that automatically look up the employee's availability, compares it to the shift list, and returns in the DV list only the shifts of the shift list that the employee can work.


So, for example, if Thomas Jefferson can't work from 10 am to 4 pm on Mondays, then the drop-down for Jefferson and Monday would only display the 5pm-1 am shift.


If this require more clarification, I'm more than happy to detail more.


Thank you again!!!
 
Hi ,


If you can upload the worksheet which you have already prepared , it'll make it easier for others to come up with formulae to do what you want. Is this possible ?


Narayan
 
Hi ,


Sorry about the delay ; I think at least I will need a lot of clarifications !


Let me put down what I have understood of your worksheet ; you can confirm / clarify , and thereafter we can proceed to what you want to do using this worksheet.


1. There are 3 sheets , Availability , Schedule and Shift List.


2. At present Availability has a list of employees , and a day-by-day schedule for all the 7 days of the week , for times from 8 in the morning till 1 at night.


3. How will this schedule be populated ? By hand , or by use of formulae ? If the latter , what are the rules ?


4. In the Schedule tab , there is a list of employees in column A , while the days of the week are spread across columns B through H ; what data is supposed to appear in the cells B3 through H7 , and how ?


5. There is a table in columns K through X , where formulae are already present ; are these to be left alone , or are these to be modified ? If so , how ?


6. The Shift List tab also has a list of shift times in column A ; is anything to be displayed in column B , C ,... ? If so , how ?


Narayan
 
No worries! I haven't been able to access this site the past few days anyway!


So, the Availability chart is filled in with an "X" for each hour that the employee is unavailable to work. I've set the conditional formatting to turn the cell black when an x is entered.


On the schedule page, those empty cells should be where the data-validated dropdowns that represent the available shifts on the shift list show up. This stuff in K-X isn't relevant.


The shift list is the list of shifts that the restaurant schedules employees. Only the stuff there matters.


So, for exmaple...

If you have a list of shifts such as...

8am-4pm

10am-6pm

3pm-10pm

4pm-1am

and employee George Washington is unavailable from 9 am to 2 pm every day, and unavailable all day Sunday, then

When you are in the schedule portion of the workbook, Washington's dropdowns from Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday ONLY show the available shifts of 3pm-10pm and 4pm to 1am, and NO Option for Sunday.


Let me know if this is any clearer! and thank you so much!
 
Hi ,


Thanks for clarifying. Let me again summarise :


1. You will work solely in the Schedule tab , where there will be drop-downs in the range B3:H7. Initially , when you start with a blank schedule , the drop-downs will display all the available shift times , for every employee.


2. Once you select a particular time on a particular day for a particular employee , this will automatically put an "X" in the corresponding cell in the Availability tab. At the same time , it will also remove that particular entry ( combination of employee + day-of-week + time-of-day ) from all the drop-downs in the Schedule tab , range B3:H7 , so that this combination cannot be selected again.


3. The list of employees is whatever is given on the Schedule and Availability tabs , in column A , range A3:A7 ; it is assumed that the list in both the tabs will follow the same order , so that formulae will not have to match employee names in the two tabs.


4. The shift list is already available in the Shift List tab.


5. You have confirmed that the table J2:K12 in the Schedule tab is not to be considered ; this is a big constraint removed !


I only hope you don't want this yesterday !


Narayan
 
Hi ,


Sorry for this , but I saw that my earlier post still had some misunderstanding.


The X's in the Availability tab will be entered by you , and are to be considered when creating the drop-downs in the Schedule tab.


Let us consider GW ; he is not available between 8 and 10 in the morning on all days , except Saturdays , when he is not available from 7 in the evening onwards. Does this mean that he is unavailable for the following shifts :


8:00 to 4:00 , 9:00 to 3:00 on all days except Saturdays

12:00 to 9:30 , 3:00 to 10:00 , 5:00 to 10:30 , 5:00 to 1:00 on Saturdays


Narayan


Narayan
 
Exactly!!


As for timing with this project--there is absolutely no deadline!! I've got forever to figure this out, haha.
 
I've been fiddling some more and started to wonder if the solution is somehow..


if/or/counta... but I can't see to get the logic right:


http://dl.dropbox.com/u/59701495/schedule%20thing.xlsx
 
Hi ,


Can you check out the following link ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21153


Please do not click on the hyperlink ; copy + paste the entire address in your browser.


I have worked out a drop-down list for George Washington , in cell I2 on the Shift List tab. This depends on the day of the week that has been selected ; on a trial basis , since I have worked it out only for one day of the week , if you want the drop-down for a different day , you have to change the formulae in the range B14:B21 ; since this is an array formula , you have to select the entire array , press F2 , and change the formula as follows :


=OFFSET($A$14:$A$21,MATCH(TRUE,$D$14:$D$21,0)-1,0,COUNTIF($D$14:$D$21,TRUE)) makes the drop-down list for SUNDAY.


Changing the range $D$14:$D$21 to $E$14:$E$21 will make it for MONDAY , $F$14:$F$21 for TUESDAY and so on. After changing the formula , press CTRL SHIFT ENTER , since it is an array formula.


If you think this is OK with you , I can develop this further.


Narayan
 
You sir are a genius!!


So, going forward, is it simply a matter of duplicating that formulae and logic for every individual employee? and how about managing the different days of the week? The single cell is spot-on. Now just for multiple days and employees...


Would it be best to use a macro? or hard-code everything in a "back of house" space? Also, could any of this be used in conjunction with Solver to say, solve for minimum labor hours or labor cost?
 
Hi ,


One step at a time !


You can put in a macro at a later stage if you want to extend the worksheet for data entry , shift time changes and other routine tasks ; for the moment , let us just carry this through so that you can do it yourself without spending too much time in going back and forth between the tabs.


Do you want me to complete the formulae for say the 5 employees whose names you have put in ; I know they are fictitious , but once the template for these 5 employees is there , you can decide how you want to proceed ; at present , looking at doing this for say 500 employees is going to be a nightmare , so what is your upper limit on the number of employees ?


Doing it for the entire week is not a problem.


Please let me know.


Narayan
 
Hi ,


Can you check out the following worksheet ?


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21161


Narayan
 
ahhh yes yes yes! Now is it just a matter of duplicating that code for each employee? is there a way to automate that? For example, if another manager were to add an employee but they didn't have the know-how in excel to update the supporting code, could the book do it automatically? Also, is there a way to add on additional stipulations such as age minimums for a closing shift, etc? let me know how I could help!
 
Hi ,


That's what I thought !


If there are going to be extensions to this worksheet , we need a way to automate the whole process ; if so , can you put down what are the likely extensions , such as this age minimum which you have mentioned ?


Even if a VBA procedure has to be written to automate the process , it will be nice to have the functional specs. on paper before the procedure is written , since making changes at a later stage will be inconvenient. Not that changes cannot be catered to ; but a change coming right after the system is implemented is more inconvenient than a change which comes 3 months or a year later.


Narayan
 
Narayan,


The main extensions will be the following:


Employees under 18 cannot work a shift that ends after midnight any night of the week except Saturday

Employees under 18 can't work a shift longer than 6 hours Monday through Thursday

Employees under 18 cannot work more than 28 hours per work


The shift list should be editable--ie, we may start having people work at 1 pm instead of 12

The shift list could change depending on the day of the week


that's all I know of for now. Will post more if any come up!
 
Hi ,


OK. This means that we need to have an entry of the employee's age also.


Secondly , you say the shift list should be editable ; this means we should have different shift lists , and in a separate table , specify which shift list applies to every employee.


Lastly , since the whole process has to be implemented using a VBA procedure , the process will be executed every time you run the procedure ; i.e. the validation lists for every employee for the 7 days of the week , will be filled in , and ready for use , only when you run the procedure. Whenever you make any change to the system viz. adding / deleting employees , changing shift lists , you will have to run the procedure so that the validation lists are updated.


Narayan
 
Yes! Would it be possible to have the age entry be an actual date? That way excel can automatically calculate when to allow the new shifts?


And the shift list doesn't need to be editable on an individual employee basis. I was thinking more along the lines of say, if, one day, management decides to REMOVE the 3pm-10pm shift, or ADD a 4pm to 11pm, or CHANGE the 5pm-1am to 6pm-1am... These changes would then apply for all of the employees on every day


thanks again!
 
Hi ,


Sure. So against each employee , we would have his age. This will be used in the age check to ensure that employees under 18 work with certain restrictions.


When a schedule needs to be changed , the change date will be specified , so that schedules will be implemented only from that date. But from what I have seen of your system so far , only the days of the week are used , not the dates. So will the dates of the change really need to be used ?


As far as the shift list is concerned , what you say is fine ; only one shift list will be used for every employee.


Can we say that the requirements are frozen , at least for the time being ?


Narayan
 
Hello!


So for the age restrictions, I was thinking it would be matched against a "Schedule Start Date" input cell. Basically, whenever the manager starts a schedule for the next week, they'll enter the date of the first day of the schedule in a cell. From this, the dates will appear above the days of the week in the schedule tab, with +1 for each cell across. Haha was that clear? Alternatively, the cell of the employee who is turning 18 could simply be conditionally formatted to change color when he/she has a birthday that week, so the manager could change the schedule manually.


And the only other thought I had for the shift list is--"Off." I think it would be a worthwhile distinction from simply a blank cell--cells that are completely blank would be interpreted as "no change availabile," while "off" means "off, but could work this day." Let me know if that makes sense!


thanks again!
 
Hi ,


I was wondering whether you were enjoying a vacation !


Since it involves a lot of data , writing the procedures would be easier if I have all the data in a workbook ; can you upload or email me a sample workbook containing data for at least 10 employees , with all the relevant data that you have mentioned , such as employees' ages ( some of them below 18 years of age , some above ) and shift changes from a certain date ; writing a procedure involves testing it thoroughly also ; it would be easier if I had all the data rather than having to generate it myself.


My email ID is narayank1026[at]gmail[dot]com


Narayan


P.S. I have not started writing any macro at present.
 
You must log in or register to reply here.
Back
Top