I will try to explain this the best I can!
I am attempting to create a schedule at work. In order to do so, I created three sheets. In the first sheet (Wkdays), I have included the names of each employee in column A. The rows (headings) I have labeled Sunday through Monday. I placed a 0 for days off and 1 for days worked for each employee.
On sheet two I have the daily assignment listed in Column A, with the headers Sunday through Monday (B1 through H1).To generate which employee will do what assignment, I used the function:=IF(Wkdays!B2=1,Wkdays!A2,"unassigned"), =IF(Wkdays!B3=1,Wkdays!A3,"unassigned"), IF(Wkdays!B2=1,Wkdays!A2,"unassigned"), =IF(Wkdays!B3=1,Wkdays!A3,"unassigned") and so on in column b. I used the same formula in each column making the appropriate adjustments to reference the sheet Wkdays.
This almost gave me what I wanted, however as a result of my formula there are several assignments listed as unassigned. I tried to figure out what formula to use so that if an employee had the day off (0), it would proceed to fill the next assignment with the next employee with the day worked (1), but I couldn't figure out how to do that , so I created a third sheet.
The third sheet is identical to sheet 2, however to get rid of the unassigned duties, I manually shifted the columns whenever an "unassigned" value appeared by cut and copying which gives me what I am looking for, but I know there must be a formula that avoid the need to manually cut and paste as I did in the third sheet.
Any help in this regard would be extremely appreciated.
-L2lgnd
I am attempting to create a schedule at work. In order to do so, I created three sheets. In the first sheet (Wkdays), I have included the names of each employee in column A. The rows (headings) I have labeled Sunday through Monday. I placed a 0 for days off and 1 for days worked for each employee.
On sheet two I have the daily assignment listed in Column A, with the headers Sunday through Monday (B1 through H1).To generate which employee will do what assignment, I used the function:=IF(Wkdays!B2=1,Wkdays!A2,"unassigned"), =IF(Wkdays!B3=1,Wkdays!A3,"unassigned"), IF(Wkdays!B2=1,Wkdays!A2,"unassigned"), =IF(Wkdays!B3=1,Wkdays!A3,"unassigned") and so on in column b. I used the same formula in each column making the appropriate adjustments to reference the sheet Wkdays.
This almost gave me what I wanted, however as a result of my formula there are several assignments listed as unassigned. I tried to figure out what formula to use so that if an employee had the day off (0), it would proceed to fill the next assignment with the next employee with the day worked (1), but I couldn't figure out how to do that , so I created a third sheet.
The third sheet is identical to sheet 2, however to get rid of the unassigned duties, I manually shifted the columns whenever an "unassigned" value appeared by cut and copying which gives me what I am looking for, but I know there must be a formula that avoid the need to manually cut and paste as I did in the third sheet.
Any help in this regard would be extremely appreciated.
-L2lgnd
