I would be grateful for some help in setting out of a formula.

I have two worksheets within a spreadsheet (Excel 2000) that I want to
be able to use to record discipline actions given to pupils in a
school.

The first worksheet (called 9LH) is being continually added to via
validation lists with dropdown boxes to speed entry

I have student names in column A, date in column D and a colour code
for a discipline slip in column F. This can be white, red, blue or
green.

The first row that is used is row 16 but I cannot give a last row with
it being added to all the time. (Would a named range help to get around
this if I used a macro to add a new line to the range each time another
student was added?)

I have a second sheet (called 9LH_Summary) which I want to use to count
how many of a particular colour slip each student has received since a
particular date.

I would like the formula to look at a name in A7 of 9LH_Summary and
then count all the instances of that colour slip (eg white) in column F
of sheet 9LH since a given date. The date I would like to be taken from
cell $A$5 in 9LH_Summary.

I would then like it to look at the name in A8 of 9LH_Summary etc and
repeat the above until the full list of students has been completed.

I hope someone can help as I cannot work out the complication of this

If any of the above is not clear please get back to me and I will try
to clarify it.

Many thanks to anyone who can help.

David

Hi David,

If you want, send me a sample workbook and I will take a stab at setting it
up.


Regards,
Howard

"david" wrote in message
oups.com...
I would be grateful for some help in setting out of a formula.

I have two worksheets within a spreadsheet (Excel 2000) that I want to
be able to use to record discipline actions given to pupils in a
school.

The first worksheet (called 9LH) is being continually added to via
validation lists with dropdown boxes to speed entry

I have student names in column A, date in column D and a colour code
for a discipline slip in column F. This can be white, red, blue or
green.

The first row that is used is row 16 but I cannot give a last row with
it being added to all the time. (Would a named range help to get around
this if I used a macro to add a new line to the range each time another
student was added?)

I have a second sheet (called 9LH_Summary) which I want to use to count
how many of a particular colour slip each student has received since a
particular date.

I would like the formula to look at a name in A7 of 9LH_Summary and
then count all the instances of that colour slip (eg white) in column F
of sheet 9LH since a given date. The date I would like to be taken from
cell $A$5 in 9LH_Summary.

I would then like it to look at the name in A8 of 9LH_Summary etc and
repeat the above until the full list of students has been completed.

I hope someone can help as I cannot work out the complication of this

If any of the above is not clear please get back to me and I will try
to clarify it.

Many thanks to anyone who can help.

David

Howard

many thanks for the offer - spreadsheet enroute

David

david wrote:
I would be grateful for some help in setting out of a formula.

I have two worksheets within a spreadsheet (Excel 2000) that I want to
be able to use to record discipline actions given to pupils in a
school.

The first worksheet (called 9LH) is being continually added to via
validation lists with dropdown boxes to speed entry

I have student names in column A, date in column D and a colour code
for a discipline slip in column F. This can be white, red, blue or
green.

The first row that is used is row 16 but I cannot give a last row with
it being added to all the time. (Would a named range help to get around
this if I used a macro to add a new line to the range each time another
student was added?)

I have a second sheet (called 9LH_Summary) which I want to use to count
how many of a particular colour slip each student has received since a
particular date.

I would like the formula to look at a name in A7 of 9LH_Summary and
then count all the instances of that colour slip (eg white) in column F
of sheet 9LH since a given date. The date I would like to be taken from
cell $A$5 in 9LH_Summary.

I would then like it to look at the name in A8 of 9LH_Summary etc and
repeat the above until the full list of students has been completed.

I hope someone can help as I cannot work out the complication of this

If any of the above is not clear please get back to me and I will try
to clarify it.

Many thanks to anyone who can help.

David