Validation of Data

Posted by Robert Dorr on September 06, 2001 12:13 AM

I am having trouble understanding why sometimes my data validation settings don't work in a User Form I am trying to create.

First: Can the result of a cell which contains a formula relying on other cells be validate itself by the promting of a "Stop" message if the result is outside of limits (lets say the result is a negative and only positive results are acceptable.) I can't anything to show up when trying to validate that cell by an "If" formula in the formula validation box.

Second (and related to above): Can a cell in which you are inputting data, and such data is being used in an adjacent cell by a formula, be validated by reference to the result in the adjacent cell. This is a bit of a loop but I can't get my head around it.

ie: I wish to input data into cells A1 to D1. Cell E1 contains a formula =(A1-B1-C1-D1). The cells are being data filled in the order of A1 to D1.
I want to validate E1 ,if it is negative, by a stop to "check entries made in A1:D1"
or validate individually B1:D1 if they, upon entry, produce a negative result in E1?
Thanks & regards........ Robert

Posted by Aladin Akyurek on September 06, 2001 12:50 AM

Robert,

Just to take up your examples:

(1)

Activate D1.
Activate Data|Validation.
Choose Custom for Allow in the Data Validation window.
Enter as formula:
=AND(COUNT(A1:C1)=3,E1>0)
Compose the message: "check entries made in A1:D1".

(2) To "validate individually",

Acivate A1.
Activate Data|Validation.
Choose Custom for Allow in the Data Validation window.
Enter as formula:
=$E$1>0
Compose a message, something like "Entry too big".
Copy A1 to B1:D1.

Aladin