1
From: QD - view profile
Date: Sat, Sep 2 2006 11:31 pm
Email: "QD"
Groups: microsoft.public.excel.misc
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author

I need help.

I have created an attendance calendar for 2006 and 2007 under two
different tabs for each employee. For each day there are four rows
where the first row is a date, second row is for code for attendance
and hours (has two cells), the third row has indication if it is
considered an occasion or not based on the attendace reason in the row
4th.

11/13/06 13
TPU 8
Occ
Unexcused Absence (Whole)

Now at the bottom in summary I need to count number of Unexcused
absences based on 12 rolling months. So I need a formula for 2007
calendar in summary where based on a current date in 2007, the formula
goes back 12 months and looks for "Unexcused Absence" and count total
occurances.

For example today is March 15th 2007. So the formula will go back
twelve months, 3 months in 2007 calendar and 9 months in 2006 calendar
and look for "Unexcuse.." and count and place the number. If it sees
Unexcuse... 5 times it will place number "5" in the summary.

I have an array formula that does this calculation to where it give me
previous years rolling twelve months total.

=COUNTIF(A$2:A$1041,"Unexcused Absence") for the current year.
This provides me totals for current year.
This could be placed in say cell A1044

In cell A1045 I have formuala
=SUMPRODUCT(--(A5:A1041="Unexcused Absence"),
--(A2:A1038DATE(year(today())-1,MONTH(TODAY()),DAY(TODAY()))))

The Rolling year will be the sum of the 2 formulae so on Sheet 2007 in
cell A1046
=A1044+'Sheet 2006'!A1045

Now the problem is calendar 2006 is already in use in the field.
I am putting this formula in 2007 calendar. But I am not sure how I can
train other employees to make connection of this two sheets.
What I am doing right now is:

1. for each employee copey the 2007 calendar into the same workbokk as
2006 but as a seprate tab.
2. Then copy the formlua for rolling twelve months for say "Unexcused
Absence" to 2006 calendar.
This will comeup with the rollin gtwelve months number for 2006.
3. Now I am cutting this from 2006 calendat and pasting it to 2007
calendar.
4. Now I have formula in a cell to count current years occurances + the
rolling twelve months.
But when I paste the nmubers from 2006 into 2007, I get "#ref" error.
5. Now I can change the formula to =A1044+A1045 and it will work. But
there four different categories that I need to do this with. and I
have several employees using this attendance controller already for
2006. The level of excel varies.

I am trying to make it as easy as possible. What can I do so the
rolling twelvemonths total can be automatically added to 2007 calendar
with minimum formula manipulation. What can I do so the #Ref error does
not happen or any better way.

I also tried to enter a Macro where once they cut copy from 2007, paste
on 2006 and cut it and paste it back on 2007, they will run a macro
where it will add "+A1045" to each category bu simply pressing "
Control R". But this deos not work as good.

Help!!!