Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A data validation question
I'm trying to understand how/why Excel does this and would like to
know if there is a way to do this differently. Here is my scenario Column-A contains numeric data 1 0 2 0 3 4 5 Column-B is a "custom" data validated field with the following formula: =OR(AND(A1=0,B1=0),AND(A10,B1=2)) If I input a value to Column-B-1 =2 the value is accepted. If I drag the cell (from bottom right) to fill down rows the value from Column-B-1 is "copied" as well. I understand this. What I can't understand is the value it copies violated the data validation formula (no error is returned) and then I must manually enter the acceptable value, 0, into Column-B-2. Is there a way to either make the value of Column-B-2 a 0 (zero) or an empty cell on a drag/fill operation? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A data validation question
Portunus wrote:
I'm trying to understand how/why Excel does this and would like to know if there is a way to do this differently. Here is my scenario Column-A contains numeric data 1 0 2 0 3 4 5 Column-B is a "custom" data validated field with the following formula: =OR(AND(A1=0,B1=0),AND(A10,B1=2)) If I input a value to Column-B-1 =2 the value is accepted. If I drag the cell (from bottom right) to fill down rows the value from Column-B-1 is "copied" as well. I understand this. What I can't understand is the value it copies violated the data validation formula (no error is returned) and then I must manually enter the acceptable value, 0, into Column-B-2. Is there a way to either make the value of Column-B-2 a 0 (zero) or an empty cell on a drag/fill operation? Thanks data is checked against validation rule only if it is entered "by keyboard". If you paste, drag or uses vba to enter value in a cell nothing is validated. Ask Microsoft why. you can use vba code to check values in worksheet_change event |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Question | Excel Discussion (Misc queries) | |||
Data validation (lis) question | Excel Worksheet Functions | |||
Data Validation Question | Excel Discussion (Misc queries) | |||
Data Validation Question | Excel Discussion (Misc queries) | |||
Data Validation Question | Excel Discussion (Misc queries) |