Prevent Negative Numbers in a column

[manubhanot]

Greetings to all! I just joined this forum so that I can learn from the experts and contribute as much as I can.

I have a situation for which I am looking a solution for:

I have three sheets in my excel file to manage inventory, namely, Demand, Supply, Inventory. Let us say that an article in Cell A2 in "Demand" = 5 and the same cell in "Supply" = 7; so we should be ok (because we have more than what is being demanded). Cell C2 in Inventory will show +2 (7 minus 5) as 2 items will be left after delivering 5.

I would want some formula that will help me restrict negative entries. Let me explain: Let us say that an article in Cell A2 in "Demand" = 7 and the same cell in "Supply" = 5. This would mean that a negative numbers will appear in Cell C2 of "Inventory" sheet. (because we have less than what is being demanded). I would want to put some kind of validation so that I can prevent this from happening.

Thanks in advance.

 

[jtakw]

Hi,

Formula references are based on my sample below, adjust to your data.

In Cell A4, Data Validation, Custom, enter formula as shown in D4, select Error Alert tab, enter error message as you wish (something like: Value entered Exceeds current Supply)

Book1
	A	B	C	D
1	Demand	Supply	Inventory
2	5	7	2
3	7	5	-2
4	5	5		TRUE
5	2	5		TRUE
6	6	5		FALSE
Sheet11

Cell Formulas
Range	Formula
C2	=B2-A2
D4	=A4<=B4

 

[JoeMo]

Assuming C2 is calculated using a formula like:
=Supply!C2-Demand!C2
what would you want the formula in Inventory cell C2 to return if the above formula returns a negative number?

 

[manubhanot]

Hello! Thanks for the reply, but unfortunately this does not suit my purpose. Demand, Supply and Inventory are in three different tabs of my excel file. The objective is to stop the user from entering anything in the demand file if the resultant is a negative number in the inventory file.

==============================================================

Hi,

Formula references are based on my sample below, adjust to your data.

In Cell A4, Data Validation, Custom, enter formula as shown in D4, select Error Alert tab, enter error message as you wish (something like: Value entered Exceeds current Supply)

	A	B	C	D
Demand	Supply	Inventory

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=B2-A2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=A4<=B4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[manubhanot]

Assuming C2 is calculated using a formula like:
=Supply!C2-Demand!C2
what would you want the formula in Inventory cell C2 to return if the above formula returns a negative number?

The objective is to stop the user from entering anything in the demand file if the resultant is a negative number in the inventory file.

 

[JoeMo]

You could use Data Validation (DV) in column C of the Demand sheet. The DV formula would be:

=Supply!$C2-$C2>=0

Alternatively, you could use a worksheet_change event module in the Demand sheet. If that's of interest, let me know.

 

[jtakw]

Hello! Thanks for the reply, but unfortunately this does not suit my purpose. Demand, Supply and Inventory are in three different tabs of my excel file. The objective is to stop the user from entering anything in the demand file if the resultant is a negative number in the inventory file.

==============================================================

That is EXACTLY what that Data Validation will do. Whenever anyone tries to enter a value in Demand A2 that is Greater than Supply, it will be rejected, and the Error Message will pop up letting the User know that it exceeds the Supply, thus, Inventory will never be Negative.

Have you tried it???

As I said, adjust formula to your data, something like this:

=A2<=Supply!B2

Put that formula in your Demand sheet subject cell (where user enters the number for Demand), reference your Supply sheet cell where you store your amount for Supply.

 

[manubhanot]

Do I need to put this formula for every cell in the sheet or can I use a generic formula for the column?

 

[manubhanot]

That is EXACTLY what that Data Validation will do. Whenever anyone tries to enter a value in Demand A2 that is Greater than Supply, it will be rejected, and the Error Message will pop up letting the User know that it exceeds the Supply, thus, Inventory will never be Negative.

Have you tried it???

As I said, adjust formula to your data, something like this:

=A2<=Supply!B2

Put that formula in your Demand sheet subject cell (where user enters the number for Demand), reference your Supply sheet cell where you store your amount for Supply.

I applied this formula successfully for the column
=$F2<=INVENTORY!$D2

Is there a way to increase the range in the formula; something like:
=($F2:R2)<=INVENTORY!$D2

 

[jtakw]

If it's only Column F and Column R, I would enter it separately, just like the way you did Column F.