existing data in case of data validation

[daidipya]

I am using data validation in number of cells. Assume a case where i
have data validation where i have restricted the value in cell no A25
not to exceed a value which is appearing at cell no A24. Now assuming
that the value at Cell A24 is 100 the data validation rightly does not
allow me to feed any value over 100 at cell no A25. Now assuming that I
have fed vale 35 at cell no. A25 it allows me to feed the value. Now in
case if i change the value of cell A24 to 23 no error report is
generated even though the Value at Cell No A25 is 35 which is greater
than the value of 23 as appearing at cell no A24.

please guide

[Ron Coderre]

daidipya:

Data Validation doesn't work the way you described. It's basically a nice
feature to restrict direct input to cells upon entry. It doesn't engage on
those cells again until there is a change to the cell....not to the
validation condition. And it doesn't prevent users from finding ways to enter
invalid data.

To do what you're looking for, you'd need to create a small VBA procedure to
trap changes to cell A24 and reflect them in A25.

Perhaps something like this (entered in the worksheet module):

'--------Start of Code------
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A24")) Is Nothing Then
If Range("A25") > Range("A24") Then
Range("A25").ClearContents
End If
End If
End Sub
'--------End of Code------

That code traps changes to cell A24.
After the change, if A25 is greater than A24....then, A25 is cleared.
Otherwise, no change to A25.

Note: By putting a vba program in your workbook, users will be prompted to
enable macros when they open the workbook. If they disable macros...that code
won't run.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"daidipya" wrote:

> I am using data validation in number of cells. Assume a case where i
> have data validation where i have restricted the value in cell no A25
> not to exceed a value which is appearing at cell no A24. Now assuming
> that the value at Cell A24 is 100 the data validation rightly does not
> allow me to feed any value over 100 at cell no A25. Now assuming that I
> have fed vale 35 at cell no. A25 it allows me to feed the value. Now in
> case if i change the value of cell A24 to 23 no error report is
> generated even though the Value at Cell No A25 is 35 which is greater
> than the value of 23 as appearing at cell no A24.
>
> please guide
>
>