Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with schedule
I have a schedule for 50 people with names in a column A and days of the week
in first row: each employee can be assigned to one of four duty stations, be off, or have vacation (that can be chosen from drop-down list) on a certain day. That works fine, I get all numbers that I need, but I need separate sheets for each duty station (4) with 7-10 employees. I tried to use Pivot Table Report to have employee names show in each duty station sheet, but that didn't work (if "John" worked at duty station 1 on Monday, but didn't on Tuesday, his name still showed up on Tuesday's duty station sheet. Is there a another way to extract names of the employees i.e. assigned to duty station 1 on Monday and so on? I also tried DGET function, but that didn't work either. Any help would be greatly appreciated. |
#2
|
|||
|
|||
One play to try out ..
In Sheet1 ------------- Assume the table below is in cols A to H, headers in row1, data from row2 down Names Mon Tue Wed Thu Fri Sat Sun Name1 DS1 DS2 DS3 DS4 DS2 DS3 DS4 Name2 DS4 DS1 Vac DS3 DS4 Off DS1 Name3 DS2 DS3 DS2 DS1 DS3 DS1 DS2 Name4 DS3 Off DS4 DS2 DS4 DS4 DS3 Name5 DS1 DS4 Vac DS3 DS1 DS2 Off etc whe DS1 = Duty Station1, DS2 = Duty Station2, etc Vac = Vacation, Off = Day-off Assuming empty cols to the right of col H Put in J1:M1 : DS1, DS2, DS3, DS4 Put in I1: =Sheet2!A1 Put in J2: =IF(INDIRECT(CHAR(MATCH($I$1,$A$1:$H$1,0)+64)&ROW( ))="","",IF(INDIRECT(CHAR( MATCH($I$1,$A$1:$H$1,0)+64)&ROW())=J$1,ROW(),"")) Copy J2 across to M2, fill down by as many rows as data is expected in cols A to H, say down to M100? (should be more than enough, since the # of people is ~ 50) In Sheet2 ------------- Let's reserve A1 for a data validation list to select the "day-of-week" Select A1 Click Data Validation Under Allow: choose List Put in "Source:" box: Mon, Tue, Wed, Thu, Fri, Sat, Sun Click OK Put headers in A2:D2 : DS1, DS2, DS3, DS4 Put in A3: =IF(ISERROR(MATCH(SMALL(Sheet1!J:J,ROW(A1)),Sheet1 !J:J,0)),"",OFFSET(Sheet1! $A$1,MATCH(SMALL(Sheet1!J:J,ROW(A1)),Sheet1!J:J,0)-1,)) Copy A3 across to D3, fill down by as many rows as was done in cols J to M in Sheet1 The above will give return nicely the schedule of names by Duty Station # from Sheet1, by the day-of-week selected in cell A1 If "Mon" is selected in A1, for the sample data, you'll get: Mon DS1 DS2 DS3 DS4 Name1 Name3 Name4 Name2 Name5 Selecting "Fri" in A1 returns: Fri DS1 DS2 DS3 DS4 Name5 Name1 Name3 Name2 Name4 And so on .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "0o0o0o0o" wrote in message ... I have a schedule for 50 people with names in a column A and days of the week in first row: each employee can be assigned to one of four duty stations, be off, or have vacation (that can be chosen from drop-down list) on a certain day. That works fine, I get all numbers that I need, but I need separate sheets for each duty station (4) with 7-10 employees. I tried to use Pivot Table Report to have employee names show in each duty station sheet, but that didn't work (if "John" worked at duty station 1 on Monday, but didn't on Tuesday, his name still showed up on Tuesday's duty station sheet. Is there a another way to extract names of the employees i.e. assigned to duty station 1 on Monday and so on? I also tried DGET function, but that didn't work either. Any help would be greatly appreciated. |
#3
|
|||
|
|||
Try this additional play as an extension
to the set-up suggested earlier .. Assuming the same table in Sheet1, cols A to G In Sheet1 ------------- Use empty cols O to V: Put in P1:V1 : Mon, Tue, Wed, Thu, Fri, Sat, Sun Put in O1: =Sheet3!A1 Put in P2: =IF(B2=$O$1,ROW(),"") Copy P2 across to V2, fill down by as many rows as data is expected in cols A to H, say down to V100 In a new Sheet3 --------------------- Like what was done in Sheet2 let's reserve A1 for a data validation list to select the 4 Duty Stations (DS1 to DS4), those on vacation (Vac) and those on day-off (Off) Select A1 Click Data Validation Under Allow: choose List Put in "Source:" box: DS1,DS2,DS3,DS4,Vac,Off Click OK Put headers in A2:G2 : Mon, Tue, Wed, Thu, Fri, Sat, Sun Put in A3: =IF(ISERROR(MATCH(SMALL(Sheet1!P:P,ROW(A1)),Sheet1 !P:P,0)),"",OFFSET(Sheet1! $A$1,MATCH(SMALL(Sheet1!P:P,ROW(A1)),Sheet1!P:P,0)-1,)) Copy A3 across to G3, fill down by as many rows as was done in cols P to V in Sheet1, viz. down to G100 thereabouts The above will return the schedule of names by day-of-week from Sheet1, by the Duty Station # (DS#) selected in cell A1 (or by those on vacation, by those on day-off) If "DS2" is selected in A1, for the sample data, you'll get: DS2 Mon Tue Wed Thu Fri Sat Sun Name3 Name1 Name3 Name4 Name1 Name5 Name3 Selecting "DS4" in A1 returns: DS4 Mon Tue Wed Thu Fri Sat Sun Name2 Name5 Name4 Name1 Name2 Name4 Name1 _______________________Name4 Selecting "Vac" in A1 returns: Vac Mon Tue Wed Thu Fri Sat Sun ______Name2 ______Name5 And so on .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
Thank you very much, your help saved me a lot of time.
"Max" wrote: Try this additional play as an extension to the set-up suggested earlier .. Assuming the same table in Sheet1, cols A to G In Sheet1 ------------- Use empty cols O to V: Put in P1:V1 : Mon, Tue, Wed, Thu, Fri, Sat, Sun Put in O1: =Sheet3!A1 Put in P2: =IF(B2=$O$1,ROW(),"") Copy P2 across to V2, fill down by as many rows as data is expected in cols A to H, say down to V100 In a new Sheet3 --------------------- Like what was done in Sheet2 let's reserve A1 for a data validation list to select the 4 Duty Stations (DS1 to DS4), those on vacation (Vac) and those on day-off (Off) Select A1 Click Data Validation Under Allow: choose List Put in "Source:" box: DS1,DS2,DS3,DS4,Vac,Off Click OK Put headers in A2:G2 : Mon, Tue, Wed, Thu, Fri, Sat, Sun Put in A3: =IF(ISERROR(MATCH(SMALL(Sheet1!P:P,ROW(A1)),Sheet1 !P:P,0)),"",OFFSET(Sheet1! $A$1,MATCH(SMALL(Sheet1!P:P,ROW(A1)),Sheet1!P:P,0)-1,)) Copy A3 across to G3, fill down by as many rows as was done in cols P to V in Sheet1, viz. down to G100 thereabouts The above will return the schedule of names by day-of-week from Sheet1, by the Duty Station # (DS#) selected in cell A1 (or by those on vacation, by those on day-off) If "DS2" is selected in A1, for the sample data, you'll get: DS2 Mon Tue Wed Thu Fri Sat Sun Name3 Name1 Name3 Name4 Name1 Name5 Name3 Selecting "DS4" in A1 returns: DS4 Mon Tue Wed Thu Fri Sat Sun Name2 Name5 Name4 Name1 Name2 Name4 Name1 _______________________Name4 Selecting "Vac" in A1 returns: Vac Mon Tue Wed Thu Fri Sat Sun ______Name2 ______Name5 And so on .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
You're welcome !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "0o0o0o0o" wrote in message ... Thank you very much, your help saved me a lot of time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Employee schedule: I want it to calculate time entered such as 11. | Excel Discussion (Misc queries) | |||
I would like to make a schedule for my dept. Who works what days . | Charts and Charting in Excel | |||
I would like to make a schedule for my dept. Who works what days . | Charts and Charting in Excel | |||
Mortgage Amortization Schedule | Excel Discussion (Misc queries) | |||
Unknown IF function parameter on amortization schedule | Excel Worksheet Functions |