Restrict Entry to cell if it is out of Stock

[vipinpalkar]

I have two sheets one is stock balance and other is sales..if my stock is less in stock balance sheet for a product entered in sales it should not allow me to enter using data validation or any formula or function. plz help me in this..I am attaching my sheet for reference.inv.xlsx

[bmouse]

Without too much fuss, I would probably pull the "Stock" column into the "Sales" sheet and put an extra column with an IF formula and some Conditional formatting, so if you enter a number greater than your stock a red light comes up.

[Glenn Kennedy]

Try this. I have added a helper column that can be hidden. As you add new orders on TO THE BOTTOM of the list, if the MOS RECENT order reduces stock to <0, an alert appears - showing the number of stock items remaining. It's an array formula.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[vipinpalkar]

thanks glenn..it works but if you can restrict it using custom data validation it would be great

[morasrikanth]

I have two sheets one is stock balance and other is sales..if my stock is less in stock balance sheet for a product entered in sales it should not allow me to enter using data validation or any formula or function. plz help me in this..I am attaching my sheet for reference.Attachment 411360

Dear Vipin,

Please find the attachment with your query solved.

Regards
M Srikanth

[vipinpalkar]

Mr. morasrikant noting is done in your attached sheet...i think you have send me the same attachment

[morasrikanth]

Mr. morasrikant noting is done in your attached sheet...i think you have send me the same attachment

Dear Vipin,

I have created a data validation for the QUANTITY column (column E) of SALES sheet which restrict from entering value more that stock available for the products.

[vipinpalkar]

its not working morasrikant. plz if u can resend the file..will be thankfull

[morasrikanth]

its not working morasrikant. plz if u can resend the file..will be thankfull

Dear Vipin,

Try this,

Select cells E6:E2000 in Sales worksheet and add data validation as "Whole number" and give values as
Minimum = 0 and
Maximum =INDEX(Stock.Balance!$F$6:$F$127,MATCH(D6,Stock.Balance!$C$6:$C$127,0))+$E6

[Glenn Kennedy]

You're welcome... and thanks for the Rep.