I need to figure out a way to prevent people from adding values if a certain cell reaches zero. I have thousands of t-shirts to distribute among several locations, and I want people to type in the amount they need, however, I only have a certain number of t-shirts produced, so the idea is to prevent people from ordering them if the stock level reaches zero. e.g I have 40 Large tees in cell C3 in cells C4:C9 I allow people from the offices to type in their desired quantity, as it is a first come first served basis if someone from one location orders all of them I want to get other cells blocked/locked and maybe greyed out.

is this doable in Excel, or Google Spreadsheet?

thanks!

CodePudding user response：

You can use data validation.

1. Select the range C4:C9. On the ribbon, select Data > Data Validation.
2. Select "Custom" and enter the formula =SUM($C$4:$C$9)<=$C$3.
   
3. Optionally, open the "Error Alert" tab and provide a custom message to be shown to users when they try to exceed the value in C3.

CodePudding user response：

your solution works perfectly when in the column, but I was asked to edit the file so the sizes are now horizontal. I have a number of tees (40) in cell C3 and I am allowing office managers to edit cells D3 to G3, but for some reason, I'm getting an error message even if the stock is lower than 40.

This is the formula I made based on yours: =SUM(D3:G3)<=$C$3

Any suggestions?

Thanks so much for all the help!

error here