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

[JessiRight77]

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

 

[Earl Kiosterud]

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

 

[JessiRight77]

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

Many thanks,
Jessi

 

[JessiRight77]

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

 

[Earl Kiosterud]

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.

 

[JessiRight77]

Okay... THANKS!!

Jessi