Prevent Negative Numbers in a column

manubhanot

New Member
Joined
Mar 6, 2018
Messages
7
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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,467
Office Version
  1. 2016
Platform
  1. Windows
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)

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Demand</td><td style=";">Supply</td><td style=";">Inventory</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">7</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">7</td><td style="text-align: right;;">5</td><td style="text-align: right;;">-2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">FALSE</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet11</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=B2-A2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">=A4<=B4</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Mar 6, 2018
Messages
7
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)

ABCD
1DemandSupplyInventory
2572
375-2
455TRUE
525TRUE
665FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet11

Worksheet Formulas
CellFormula
C2=B2-A2
D4=A4<=B4

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

manubhanot

New Member
Joined
Mar 6, 2018
Messages
7

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You could use Data Validation (DV) in column C of the Demand sheet. The DV formula would be:
Code:
=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

Well-known Member
Joined
Jun 29, 2014
Messages
5,467
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

Code:
=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

New Member
Joined
Mar 6, 2018
Messages
7
Do I need to put this formula for every cell in the sheet or can I use a generic formula for the column?
 

manubhanot

New Member
Joined
Mar 6, 2018
Messages
7
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:

Code:
=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

Well-known Member
Joined
Jun 29, 2014
Messages
5,467
Office Version
  1. 2016
Platform
  1. Windows
If it's only Column F and Column R, I would enter it separately, just like the way you did Column F.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top