Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Valadation
I have the following Data
Col A Col B Col C 2 3 =A1-B1 The user enters data in Cell A1 Or Cell B1 and Cell C1 Calculates the results I want the following If the user enters any value in Cell A1 Or Cell B1 and the Value computed in Cell C1 returns to Negative . he gets a pop up saying "Invalid Range" Eg in the above Cell C Will Return -1 in such case I want to get a pop up In valid Range If Cell C1 Is Positive no message should be displayed I tried Data Valadation but It does not work with Formulas . . . Or Does it work with Formula which I am not aware of ? Thank You |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Valadation
Use Data Validation in A1 and B1 with a custom type and a formula of
=$C1=0 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "claude jerry" wrote in message ... I have the following Data Col A Col B Col C 2 3 =A1-B1 The user enters data in Cell A1 Or Cell B1 and Cell C1 Calculates the results I want the following If the user enters any value in Cell A1 Or Cell B1 and the Value computed in Cell C1 returns to Negative . he gets a pop up saying "Invalid Range" Eg in the above Cell C Will Return -1 in such case I want to get a pop up In valid Range If Cell C1 Is Positive no message should be displayed I tried Data Valadation but It does not work with Formulas . . . Or Does it work with Formula which I am not aware of ? Thank You |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Valadation
Try something like this:
Select A1:B1 From the Excel main menu: <data<validation Allow: Custom Formula: =($A$1-$B$1)0 Select the Error Alert tab Title: INVALID RANGE Error message: The A1 value must be larger than the B1 value. Click the [OK] button Does that help? *********** Regards, Ron XL2002, WinXP "claude jerry" wrote: I have the following Data Col A Col B Col C 2 3 =A1-B1 The user enters data in Cell A1 Or Cell B1 and Cell C1 Calculates the results I want the following If the user enters any value in Cell A1 Or Cell B1 and the Value computed in Cell C1 returns to Negative . he gets a pop up saying "Invalid Range" Eg in the above Cell C Will Return -1 in such case I want to get a pop up In valid Range If Cell C1 Is Positive no message should be displayed I tried Data Valadation but It does not work with Formulas . . . Or Does it work with Formula which I am not aware of ? Thank You |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Valadation
Right click the sheet tab and paste this in.
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) If Cells(1, 3).Value < 0 Then MsgBox "Invalid Range" End If Mike End Sub "claude jerry" wrote: I have the following Data Col A Col B Col C 2 3 =A1-B1 The user enters data in Cell A1 Or Cell B1 and Cell C1 Calculates the results I want the following If the user enters any value in Cell A1 Or Cell B1 and the Value computed in Cell C1 returns to Negative . he gets a pop up saying "Invalid Range" Eg in the above Cell C Will Return -1 in such case I want to get a pop up In valid Range If Cell C1 Is Positive no message should be displayed I tried Data Valadation but It does not work with Formulas . . . Or Does it work with Formula which I am not aware of ? Thank You |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Valadation
Perfect !
Thanks Guys "Ron Coderre" wrote: Try something like this: Select A1:B1 From the Excel main menu: <data<validation Allow: Custom Formula: =($A$1-$B$1)0 Select the Error Alert tab Title: INVALID RANGE Error message: The A1 value must be larger than the B1 value. Click the [OK] button Does that help? *********** Regards, Ron XL2002, WinXP "claude jerry" wrote: I have the following Data Col A Col B Col C 2 3 =A1-B1 The user enters data in Cell A1 Or Cell B1 and Cell C1 Calculates the results I want the following If the user enters any value in Cell A1 Or Cell B1 and the Value computed in Cell C1 returns to Negative . he gets a pop up saying "Invalid Range" Eg in the above Cell C Will Return -1 in such case I want to get a pop up In valid Range If Cell C1 Is Positive no message should be displayed I tried Data Valadation but It does not work with Formulas . . . Or Does it work with Formula which I am not aware of ? Thank You |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Valadation
Hi Ron / Bob
I Just modifying the file and now am facing another Problem ColA Col B Col C Col D . . . Col H Col ? Quota Day1 Day2 Day3 . . . Used Balance 4 Bana Oran Melo =Counta(B3:H3) =A3 - H3 In The above the user has to enter his quota Fig. . a Digit below 7 in cell A3 In Cells B3 to H3, I am using List Valadation . .i.e It allows the user to choose from a drop down list of Fruits and the list contains "Bana, Oran, Melo, Grap", He cannot enter any other Fruit which is not in the list. In H3 . It counts the number of times he ate the fruits The Bal Col Calculates the Balance remaining from his quota. I want to use a formula which will give a pop up for the user "Your Quota is Finished" As soon as the balance becomes Zero. . and prevent the user from entering more entries in Day Cells . . i.e balance cannot be negative Now as I have already Used the Valadation to get Drop down list of fruits in cells (B3:H3) I cannot use another Valadation criteria for the same cells (B3:H3) Is there a way to get a pop up using the cell which Shows my Balance =If(My balance becomes negative,Pop up "Your Bal IS over",Else"No message") Thanks Claude "Ron Coderre" wrote: Try something like this: Select A1:B1 From the Excel main menu: <data<validation Allow: Custom Formula: =($A$1-$B$1)0 Select the Error Alert tab Title: INVALID RANGE Error message: The A1 value must be larger than the B1 value. Click the [OK] button Does that help? *********** Regards, Ron XL2002, WinXP "claude jerry" wrote: I have the following Data Col A Col B Col C 2 3 =A1-B1 The user enters data in Cell A1 Or Cell B1 and Cell C1 Calculates the results I want the following If the user enters any value in Cell A1 Or Cell B1 and the Value computed in Cell C1 returns to Negative . he gets a pop up saying "Invalid Range" Eg in the above Cell C Will Return -1 in such case I want to get a pop up In valid Range If Cell C1 Is Positive no message should be displayed I tried Data Valadation but It does not work with Formulas . . . Or Does it work with Formula which I am not aware of ? Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Hyperlink with Data Valadation | Excel Discussion (Misc queries) | |||
Using a Hyperlink with Data Valadation | Excel Discussion (Misc queries) | |||
populate data valadation list by criteria | Excel Worksheet Functions | |||
Data Valadation - | Excel Worksheet Functions | |||
Data Valadation help | Excel Worksheet Functions |