Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How Can I Prevent Data Entry In Same Category on the Same Date?
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 ------------------------------------------- wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How Can I Prevent Data Entry In Same Category on the Same Date?
Cool! I think this is exactly what I need, and I am very appreciative
of your help. Many thanks, Jessi |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How Can I Prevent Data Entry In Same Category on the Same Date?
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How Can I Prevent Data Entry In Same Category on the Same Date?
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 ------------------------------------------- wrote in message oups.com... 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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
How Can I Prevent Data Entry In Same Category on the Same Date?
Okay... THANKS!!
Jessi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent a Weekend date entry | Excel Worksheet Functions | |||
prevent data entry into cell? | Excel Worksheet Functions | |||
Validate in WS to prevent dual data entry | Excel Worksheet Functions | |||
How can I prevent loosing formula during data entry? | Excel Discussion (Misc queries) | |||
Data validation to prevent duplicate entry. | Excel Discussion (Misc queries) |