Prevent Negative Inventory

[Peter888]

As Title said, I have 3 Column Which is Inventory A1, Sales B1, Final Stock C1. I want to make Data Validation to prevent user from entering Sales Value that exceeds my final stock value and resulting negative final stock value. Is there any simple formula for it. I want this to applies to whole final stock column. Thank you in advance

 

[Joe4]

Please post a sample of your data so we can see what your data structure looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Peter888]

As Title said, I have 3 Column Which is Inventory A1, Sales B1, Final Stock C1. I want to make Data Validation to prevent user from entering Sales Value that exceeds my inventory stock and resulting negative final stock value. Is there any simple formula for it. I want this to applies to whole final stock column. Thank you in advance

Hello Sorry for not posting the data sample earlier. What i want is to prevent cell Total Inventory Column from being negative (As you can see Total Inventory of Apple were -3 on cell G6). I want a data validation formula that gives Error Message whenever a user trying to input sales value that exceed Last Total Inventory Value. Example, Apples beginning stock were 10. Theres Apple Sales on 1 January and 10 January which were 2 and 1 respectively. But on 13 January, the user input Apple Sales with 10 Value while we only have 7 Apples left in our inventory. How do i prevent user to input value more than our last inventory value?)

Book1
	C	D	E	F	G
5		Name	Beginning Inventory	Sales	Total Inventory
6		Apple	10	13	-3
7		Orange	8	2	6
8		Grape	6	4	2
9
10		Sales
11	Date	Name	QTY
12	01-Jan	Apple	2
13	03-Jan	Orange	2
14	10-Jan	Apple	1
15	12-Jan	Grape	4
16	13-Jan	Apple	10
Sheet1

Cell Formulas
Range	Formula
F6:F8	F6	=SUMIF($D$12:$D$27,D6,$E$12:$E$27)
G6:G8	G6	=E6-F6

 

[Joe4]

I am not sure how to do it with Data Validation, but I can think of a way to do it using VBA (it will be automated VBA code that will run automatically upon cell entry).
Is that an acceptable solution?
You do not need to know anything about VBA to use it - it will be a "plug-and-play" solution.

 

[Fluff]

How about

+Fluff 1.xlsm
	C	D	E	F	G
5		Name	Beginning Inventory	Sales	Total Inventory
6		Apple	10	10	0
7		Orange	8	2	6
8		Grape	6	4	2
9
10		Sales
11	Date	Name	QTY
12	01/01/2021	Apple	2
13	03/01/2021	Orange	2
14	10/01/2021	Apple	1
15	12/01/2021	Grape	4
16	13/01/2021	Apple	7
Lists

Cell Formulas
Range	Formula
F6:F8	F6	=SUMIF($D$12:$D$27,D6,$E$12:$E$27)
G6:G8	G6	=E6-F6

Cells with Data Validation
Cell	Allow	Criteria
D12:D16	List	=$D$6:$D$8
E12:E16	Whole number	between 1 and SUMIFS($E$6:$E$8,$D$6:$D$8,D12)-SUMIFS(E$11:E11,D$11:D11,D12)

 

[mikerickson]

Data Validation with the formula =(($A1-$B1)>0) would work.