Relating One cell Value to another to the left of it.

[Gordon Buchanan]

Below is an extract from an excel spreadsheet I am
currently working on. There are three columns, one with
standard hours (Stnd Hrs.) one with Overtime hours (Ovtm
Hrs.) and one with an Overtime Code (Ovtm Code).

Sunday
03-Aug-03
Stnd Hrs. Ovtm Hrs. Ovtm Code
7 2 A
7 3 B
5 3 C
5 3 C
3 6 F
4 2 D
5 6 B
4 3 A
0 3 F
0 6 C

What I require is to be able to relate the (Ovtm Code)
value in one cell to the (Ovtm Hrs) value in the preceding
cell on the same row. I then require to total up the
(Ovtm Hrs.) value for each (Ovtm Code) value. I would
like to then display these new values on a second sheet.
As laid out below.



Sickness Holiday Maternity Leave Lateness Union Duties
(A) (B) (C) (D) (E)
5 9 9 2 0


The first row contains the (Ovtm Code)description, the
second row contains the acutal (Ovtm Code) and the third
row conains the totalled up (Ovtm Hrs.) for that
particular (Ovtm Code).

Hope someone could be of help to me.

Many Thanks


Gordon Buchanan

 

[Anon]

Below is an extract from an excel spreadsheet I am
currently working on. There are three columns, one with
standard hours (Stnd Hrs.) one with Overtime hours (Ovtm
Hrs.) and one with an Overtime Code (Ovtm Code).

Sunday
03-Aug-03
Stnd Hrs. Ovtm Hrs. Ovtm Code
7 2 A
7 3 B
5 3 C
5 3 C
3 6 F
4 2 D
5 6 B
4 3 A
0 3 F
0 6 C

What I require is to be able to relate the (Ovtm Code)
value in one cell to the (Ovtm Hrs) value in the preceding
cell on the same row. I then require to total up the
(Ovtm Hrs.) value for each (Ovtm Code) value. I would
like to then display these new values on a second sheet.
As laid out below.



Sickness Holiday Maternity Leave Lateness Union Duties
(A) (B) (C) (D) (E)
5 9 9 2 0


The first row contains the (Ovtm Code)description, the
second row contains the acutal (Ovtm Code) and the third
row conains the totalled up (Ovtm Hrs.) for that
particular (Ovtm Code).

Hope someone could be of help to me.

Many Thanks


Gordon Buchanan

Let's say your hours and codes table occupied Sheet1!A4:C13 and that the
sickness code "A" was in Sheet2!A2.
In Sheet2!A3 you could put this formula:
=SUMPRODUCT((Sheet1!$C$4:$C$13=A$2)*Sheet1!$B$4:$B$13)
You could then copy this across to cells B3, C3, D3 and E3 and it would
adjust to refer to the other codes.

 

[Dave Peterson]

You also may want to look at Data|Pivottable.

Select your range (only the last row of headers, though)
Data|Pivottable
Follow the wizard until you get to a dialog sheet with a Layout button on it.
Hit that button
Drag the "ovtm code" to the column area
Drat the "ovtm hrs" to the Data area
(if that doesn't say "sum of ovtm hrs", double click on it and choose Sum)

Click finish.

When I did it, I got something like:

Sum of Ovtm Hrs. Ovtm Code
A B C D F Grand Total
Total 5 9 12 2 9 37

If you change the labels (A becomes "A-Sickness"), it might even look nicer.