Neo1 wrote...
yes I can but still the error message doesnt appear when i change the
sell from the other worksheet only if i change it from the one that
has been linked which is in the worksheet of the validation check..and
i dont want to enter the value here i want to enter the value in the
other worksheet...what can i do?
Validation is EASILY defeated. If you set validation on cell A1 to
accept only values > 0, then define X referring initially to 100, then
cell A1 accepts the formula =X since it initially evaluates to 100
which is > 0. Then change the definition of X to -5000. A1 will change
to -5000 without any validation error message being displayed.
If you're applying a validation rule to cell X99 in worksheet A in
workbook 1.xls based on a corresponding value in cell K43 in worksheet
B in workbook 2.xls, you can change the latter cell at will so that the
entry in the former cell becomes invalid and Excel won't complain at
all. Only when you try to change the former cell would the new value of
the latter cell come into play.
Data > Validation is barely capable of preventing honest errors by
naive but dilligent users. It's little or no help against lazy users
who can defeat validation by pasting stuff into cells from other
applications, and it's way too weak to prevent intentionally invalid
entries.
The only way to impose real validation is using Change and Calculate
event handlers coupled with do-nothing but widely used udfs that would
return #NAME? errors (and thus screw up the formulas which call it) if
users try to disable macros in order to bypass the event handlers.
