how to return an error if Cell C2 is greater than the sum of Cells A2 and B2

[MAHENDRA WAMAN]

I have a table with 5 columns. The First Column is an Opening Balance, second column is Received Quantity, third column is Issued Quantity, fourth column is Closing Balance and the fifth column is Date. I am using this table to maintain a simple inventory. The very first cell under the Opening Balance is entered as an integer value initially. Then onwards only the Received Quantity and Issued Quantity values are entered and the Closing Balance and the Opening Balance values are calculated using formula.
E.g.
A1,B1,C1,D1,E1
A2,B2,C2,D2,E2
A3,B3,C3,D3,E3
A4,B4,C4,D4,E4
.
.
.

The A1 value is entered as 10. The Received Quantity is 0, Issued Quantity is 0 and thus the Closing Balance is calculated as =A1+B1-C1 which will give us value as 10.
The value for A2 is taken as the value of D1 i.e. closing balance of previous day which is 10.
Now when the Quantity is issued, I want to make sure that it does not cross the sum of Opening Balance and Received Quantity so that the Closing Balance does not run into a negative value.
I tried it using Data Validation but could not achieve it; tried validation options on cell C2 as well as on cell D2.
Can anybody guide how to achieve this?
Thanks.

 

[T. Valko]

I have a table with 5 columns. The First Column is an Opening Balance, second column is Received Quantity, third column is Issued Quantity, fourth column is Closing Balance and the fifth column is Date. I am using this table to maintain a simple inventory. The very first cell under the Opening Balance is entered as an integer value initially. Then onwards only the Received Quantity and Issued Quantity values are entered and the Closing Balance and the Opening Balance values are calculated using formula.
E.g.
A1,B1,C1,D1,E1
A2,B2,C2,D2,E2
A3,B3,C3,D3,E3
A4,B4,C4,D4,E4
.
.
.

The A1 value is entered as 10. The Received Quantity is 0, Issued Quantity is 0 and thus the Closing Balance is calculated as =A1+B1-C1 which will give us value as 10.
The value for A2 is taken as the value of D1 i.e. closing balance of previous day which is 10.
Now when the Quantity is issued, I want to make sure that it does not cross the sum of Opening Balance and Received Quantity so that the Closing Balance does not run into a negative value.
I tried it using Data Validation but could not achieve it; tried validation options on cell C2 as well as on cell D2.
Can anybody guide how to achieve this?
Thanks.

So, do you want to make sure the amount issued does not exceed the amount in inventory?

 

[MAHENDRA WAMAN]

So, do you want to make sure the amount issued does not exceed the amount in inventory?

Yes, you got it right.

 

[jim may]

In your Sheet Module Code window paste in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Range("D" & Target.Row) < 0 Then
MsgBox "Closing Balance is Negative; Correct and Try Again"
Target.Select
Target = ""
Application.EnableEvents = True
Exit Sub
Else
Range("E" & Target.Row).Value = Date
End If
Application.EnableEvents = True
End Sub

 

[T. Valko]

Yes, you got it right.

Ok, you should be able to do that with data validation.

Select cell C2.

Data Validation
Allow: Custom
Formula: =C2<=A2+B2

 

[MAHENDRA WAMAN]

In your Sheet Module Code window paste in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Range("D" & Target.Row) < 0 Then
MsgBox "Closing Balance is Negative; Correct and Try Again"
Target.Select
Target = ""
Application.EnableEvents = True
Exit Sub
Else
Range("E" & Target.Row).Value = Date
End If
Application.EnableEvents = True
End Sub

Thanks a lot... the code is very useful!
Automatically updating the date is superb!!
If I want to use this code for multiple sheets, then I will have to copy it for each sheet separately or is there any other alternate / simple way to implement this code for multiple sheets.
Thanks again.

 

[MAHENDRA WAMAN]

Ok, you should be able to do that with data validation.

Select cell C2.

Data Validation
Allow: Custom
Formula: =C2<=A2+B2

I tried this... but didn't work.

 

[T. Valko]

I tried this... but didn't work.

Works for me.