How Can I Prevent Data Entry In Same Category on the Same Date?

Hello... I hope that someone can help me.

I have a worksheet for which I randomly enter scores for students in
several categories throughout the day.

Column Headings:

Date StudentName Category1 Category2 Category3, etc.


I would like for Excel to display a warning message if I attempt to
enter a score in the SAME category on the SAME day.

Does anyone know how I may do something like this?

Thanks!!!
Jessi

Jessi,

Select the Category1 cells, C2:C10 in this example. The dates are in
A2:A10. The following presumes C2 is the active (white) cell of your
selection. In Data - Validation - Custom:

=SUMPRODUCT(($A$2:$A$10=A2)*($C$2:$C$10<>""))<=1

The above will give you a raspberry if you try to enter a second score of a
given date into C2 with the same date as any that's already there. This is
for any student.

If this is on a per student basis, use:

=SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10=B2)*($C$2:$C$10<>""))<=1

where student names are in B2:B10
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

<[email protected]> wrote in message
news:[email protected]...
> Hello... I hope that someone can help me.
>
> I have a worksheet for which I randomly enter scores for students in
> several categories throughout the day.
>
> Column Headings:
>
> Date StudentName Category1 Category2 Category3, etc.
>
>
> I would like for Excel to display a warning message if I attempt to
> enter a score in the SAME category on the SAME day.
>
> Does anyone know how I may do something like this?
>
> Thanks!!!
> Jessi
>

Cool! I think this is exactly what I need, and I am very appreciative
of your help.

Many thanks,
Jessi

As a followup... I am trying to understand the logic behind the
SumProduct formula, and I don't quite get it. Is it somewhere along
the lines of:

For each row: the formula will compare the values in column A (the
Date) and if it finds a match it will assign a value of 1 (true) to the
Date array; otherwise 0 (false). Then it moves to column B (the
student name), and if it finds more than one occurrence of the
student's name in column B, then it again assigns a value of 1;
otherwise 0. The process is repeated in column C (category) for
whether the cell contains a value. Then it adds the values together.

Now this is where I get lost:

Why should the result be <= 1 (rather than 2)? Because it will be
possible to have more than one occurrence of the same date in column A
(a value of 1); and more than one occurrence of the student's name in
column B (another value of 1). Only in column C do I want to restrict
a second matching sequence. So, if I have matches in columns A and
B, that would be 1 + 1 = 2. What am I missing?

Many, many thanks for your help!

Jessi

Jessi,

You've entered this Data Validation stuff only in the cells of column C,
even though the formula looks at A and B, so validation error stops will
occur only with entries in column C.

> Why should the result be <= 1 (rather than 2)?

If the SUMPRODUCT has summed more than 1 (one is for the row we're in, any
others are the duplicates), then we want to produce a FALSE in our
validation formula, since it wants TRUE to not throw up the raspberry.

By the way, we could have used

=SUM(($A$2:$A$10=A2)*($B$2:$B$10=B2)*($C$2:$C$10<>""))<=1

just as well -- we're not really using the PRODUCT part of SUMPRODUCT. But
would have to enter it as an array formula (Ctrl-Shift-Enter), if used in a
cell. Oddly, it works in Data Validation. Don't write back and ask why, on
account of I don't know.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

<[email protected]> wrote in message
news:[email protected]...
> As a followup... I am trying to understand the logic behind the
> SumProduct formula, and I don't quite get it. Is it somewhere along
> the lines of:
>
> For each row: the formula will compare the values in column A (the
> Date) and if it finds a match it will assign a value of 1 (true) to the
> Date array; otherwise 0 (false). Then it moves to column B (the
> student name), and if it finds more than one occurrence of the
> student's name in column B, then it again assigns a value of 1;
> otherwise 0. The process is repeated in column C (category) for
> whether the cell contains a value. Then it adds the values together.
>
> Now this is where I get lost:
>
> Why should the result be <= 1 (rather than 2)? Because it will be
> possible to have more than one occurrence of the same date in column A
> (a value of 1); and more than one occurrence of the student's name in
> column B (another value of 1). Only in column C do I want to restrict
> a second matching sequence. So, if I have matches in columns A and
> B, that would be 1 + 1 = 2. What am I missing?
>
> Many, many thanks for your help!
>
> Jessi
>

Okay... THANKS!!

Jessi