Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
existing data in case of data validation
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
existing data in case of data validation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA | Excel Worksheet Functions | |||
VBA Code | Excel Discussion (Misc queries) | |||
reminder notifications in a column | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions | |||
Spellnumber | Excel Worksheet Functions |