Data Valadation

[claude jerry]

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

 

[Bob Phillips]

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)

 

[Ron Coderre]

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

 

[Mike]

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]

Perfect !

Thanks Guys

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

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

 

[claude jerry]

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

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

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