i would like to map a spreadsheet to a calendar so that as we track
required staff levels to acutal the daa in the cells would populate a
calendar.
In the approach described below, Sheet2 has the staff levels and Sheet1
has the calendar.
In Sheet2, use these columns:
A: Name of a project
B: First date of the project
C: Last date of the project
D: Required staff level of the project
Put the source data in these columns.
Now go to Sheet1.
In A1 put the date where the calendar should start; e.g.,
01/01/2009
B1:M1 will be the column headings; one column per month of the calendar.
In B1, put
=EOMONTH($A$1,COLUMN()-2)
and copy rightward to M1. Select B1:M1 and use
Format > Cells > Custom >
With this as the "Type"
mmm yy
A1:A32 will indicate days of the month; one row per day.
In A2 put
=ROW()-1
and copy downward to A32.
To fill in the calendar, start by putting this in B2:
=IF(DATE(YEAR(B$1),MONTH(B$1),$A2)>B$1,"",
SUMIF(Sheet2!$C:$C,">="&DATE(YEAR(B$1),MONTH(B$1),$A2),
Sheet2!$D:$D)-
SUMIF(Sheet2!$B:$B,">"&DATE(YEAR(B$1),MONTH(B$1),$A2),
Sheet2!$D:$D))
Then extend B2 right to M2. Then extend B2:M2 down to B32:M32.
If you need to blank out weekend days, insert a test like
IF(WEEKDAY(...),...).
Modify to suit.
(I have Excel 2003.)
