Military leave tracking

[Anthony]

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.

 

[Max]

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 are:
=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