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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JessiRight77@yahoo.com - 07 Jun 2005 21:22 GMT

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

Reply to this Message

Earl Kiosterud - 07 Jun 2005 23:39 GMT

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

Signature

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

> Hello... I hope that someone can help me.
>
[quoted text clipped - 12 lines]
> Thanks!!!
> Jessi

Reply to this Message

JessiRight77@yahoo.com - 08 Jun 2005 01:30 GMT

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

Many thanks,
Jessi

Reply to this Message

JessiRight77@yahoo.com - 08 Jun 2005 13:30 GMT

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

Reply to this Message

Earl Kiosterud - 09 Jun 2005 03:39 GMT

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.

Signature

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

> 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
[quoted text clipped - 20 lines]
>
> Jessi

Reply to this Message

JessiRight77@yahoo.com - 10 Jun 2005 19:24 GMT

Okay... THANKS!!

Jessi

Reply to this Message