Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military leave tracking
I would like to design an excel workbook to track leave for Soldiers
in an effort to make sure that I have sufficient manpower to complete all my missions. I would like it to do the following: 1. On sheet 1, input rank and name, leave start and end dates 2. Pass this information into a calendar on sheet 2 (already created) by adding Soldier info to all days on which Soldier will be on leave 3. Subtract number of Soldiers who are on leave on a given date from pool of available Soldiers which I can place either on sheet 1 or another sheet 4. Compare Soldiers not on leave to manpower requirements to complete platoon's missions and give either a YES or NO response to whether we are mission capable. I am currently deployed to Iraq and as you can imagine, do not have a whole lot of time to try to figure this out alone so I am asking the experts for help. Any that you can give will be greatly appreciated by me and my Soldiers. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Military leave tracking
One possible formulas driven model ..
Illustrated in this sample: http://freefilehosting.net/download/43h5f Military Leave Tracking Model.xls In sheet: x (Leave applied records) Pool of available soldiers Staff#-Rank-Name is assumed listed in G1:I20 (used in vlookup) 1. Select Staff# from droplist in col A, a vlookup populates rank and name in cols B and C 2. Leave start/end dates are entered in cols D & E Point formulas in B2:C2, copied down a =IF($A2="","",VLOOKUP($A2,$G:$I,2,0)) =IF($A2="","",VLOOKUP($A2,$G:$I,3,0)) In sheet: y (Auto-"Leave Calendar") Dates are filled in B1 across, eg: 1-Jan-09, 2-Jan-09, etc In B2, copied across/filled down to populate: =IF(AND(x!$D2<=B$1,x!$E2=B$1),x!$C2,"") auto-returns the names from x under the appropriate dates according to the start/end dates indicated in x Then In sheet: z (Summary) In cols A to G, you could have the following col headers in A1:G1 : Date #in pool #on leave #available Scheduled Mission# Mission Minimum Manpower Reqt Mission Capable? with point formulas: In A2: =INDEX(y!$B$1:$CM$1,ROWS($1:1)) In B2: =COUNTA(x!$G$2:$G$20) In C2: =SUMPRODUCT(--(OFFSET(y!$B$2:$B$20,,ROWS($1:1)-1)<"")) In D2: =B2-C2 In E2: =MOD(ROWS($1:1)-1,5)+1 In F2: =IF(E2="","",VLOOKUP(E2,{1,15;2,16;3,17;4,18;5,16} ,2,0)) In G2: =IF(F2="","",IF(D2=F2,"Yes","No")) A2:G2 is copied down to return the required auto-tracking results. Note that the formulas for cols E & F (Scheduled Mission#, Mission Minimum Manpower Reqt) are merely illustrative to complete the model, you would replace these with your own requirements/hard figures. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "Anthony" wrote in message ... I would like to design an excel workbook to track leave for Soldiers in an effort to make sure that I have sufficient manpower to complete all my missions. I would like it to do the following: 1. On sheet 1, input rank and name, leave start and end dates 2. Pass this information into a calendar on sheet 2 (already created) by adding Soldier info to all days on which Soldier will be on leave 3. Subtract number of Soldiers who are on leave on a given date from pool of available Soldiers which I can place either on sheet 1 or another sheet 4. Compare Soldiers not on leave to manpower requirements to complete platoon's missions and give either a YES or NO response to whether we are mission capable. I am currently deployed to Iraq and as you can imagine, do not have a whole lot of time to try to figure this out alone so I am asking the experts for help. Any that you can give will be greatly appreciated by me and my Soldiers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Military time... | Excel Discussion (Misc queries) | |||
How to keep 0 for military time | Excel Discussion (Misc queries) | |||
How to insert tracking numbers into my webpage for RMA tracking | Excel Discussion (Misc queries) | |||
I need a template for Employee annual leave tracking | Excel Discussion (Misc queries) | |||
Tracking annual leave | New Users to Excel |