Finding cell references

Hi

I have posted something similar to this before, and got=20
near the answer, but not quite. Any ideas?

Sheet 1, called "Unavailability", contains the following:

A B C D E F G H
1 Unit Used Not Used 5 1-Feb 2-Feb 3-Feb 4-Feb
2 Maths =A35 =A310 =A315 a
3English =A36 =A312 =A318 a ab b
4 Music =A37 =A314 =A321 abc b abcde
5 P.E. =A310 =A320 =A330
6Geog =A315 =A330 =A345
7History =A34 =A38 =A312
8Drama =A33 =A36 =A39
9Science =A37.50 =A315 =A322.50 a
10 .
11 .
.. .
.. .

Column A lists the Units in a school.
Columns B,C and D give various amounts that are attributed=20
to Units based on a set of conditions (Used, Not Used and=20
5 days Unavailability)
The Range E2:H11 (which is much bigger but is simplified=20
here for presentation) gives the reference of faults that=20
have affected the corresponding Unit on the corresponding=20
day.


The second sheet, called "Council Usage", contains the=20
following:

A B C D E F G H
1 Unit - - - 1-Feb 2-Feb 3-Feb 4-Feb
2 Maths - - -=09
3 English - - - y y
4 Music - - - y =09
5 P.E. - - - y
6 Geog - - - =09
7 History - - -
8 Drama - - -
9 Science - - -
10 .
11 .
.. .
.. .

Where a "y" represents that the Unit has been Used on the=20
corresponding day.


The Third sheet, called "Consecutive Unavailability",=20
contains the following:

A B C D E F G H
1 Unit - - - 1-Feb 2-Feb 3-Feb 4-Feb=09
2 Maths - - - 1
3 English - - - 1 2 3
4 Music - - - 1 2 3 =09
5 P.E. - - -
6 Geog - - -=09
7 History - - -
8 Drama - - -
9 Science - - - 1
10 .
11 .
.. .
.. .

The numbers in this sheet represent the number of=20
consecutive days that a Unit has been Unavailable



The Formula I want would do the following:

.. Find all occurrences of the letter in question in=20
the "Unavailability" sheet. (I.e. find all occurrences=20
of "a");
.. For each occurrence of "a", check the corresponding cell=20
in "Council Usage". If that cell contains a "y", then the=20
amount to be added is the amount in column B of=20
the "Unavailability" sheet (no matter what the value is in=20
the corresponding cell of the "Consecutive Unavailability"=20
sheet);
.. If the corresponding cell in "Council Usage" does not=20
contain a "y", then the amount to be added is the amount=20
in column C of the "Unavailability" Sheet (if the=20
corresponding cell in "Consecutive Unavailability" is less=20
or equal to 5), or the amount in Column D of=20
the "Unavailability" sheet (if the corresponding cell=20
in "Consecutive Unavailability" is greater than 5).

For example, Fault "a" occurs in the Maths Unit on 1-Feb=20
(cell E2 of the "Unavailability" Sheet, and the Unit is=20
not used (there is no "y" in cell E2 of the "Council=20
Usage" sheet) and the Unit has been consecutively=20
Unavailable for less than 5 days (cell E2 in=20
the "Consecutive Unavailability" sheet has a "1"),=20
therefore the deduction is the amount in column C of=20
the "Unavailability" Sheet (in fact, cell C2), which is=20
=A310.

Fault "a" also occurs in cell E3 of the "Unavailability"=20
Sheet (English unit on 1-Feb), and that Unit is Used on=20
that day (there is a "y" in cell E3 of the "Council Usage"=20
sheet), therefore the deduction is the amount in column B=20
of the "Unavailability" Sheet (in fact, cell B3), which is=20
=A36.

If this was continued for all occurrences of "a", the=20
total attributed to fault "a" would then be:

.. =A310 (as above)
.. =A36 (as above)
.. =A312 (English Unavailable on 2-Feb, Unit not Used, Cons.=20
Unav =3D 2)
.. =A37 (Music Unavailable on 1-Feb, Unit is Used)
.. =A314 (Music Unavailable on 3-Feb, Unit not Used, Cons.=20
Unav =3D 3)
.. =A315 (Science Unavailable on 1-Feb, Unit not Used, Cons.=20
Unav =3D 1)

which would give a total of =A364 attributable to fault "a".

I need the formula to calculate the amount attributable to=20
fault "a", fault "b", fault "c", and so on.