Get error message for negative numbers

[sf1221]

I have a spreadsheet that contains a bed count for a facility. This is for scheduling purposes to ensure we do not overbook our clients, the row in question sums up the previous days "beds available count" and subtracts the (new intakes minus the discharges) to keep the beds available count accurate. The formula looks like this =(C38)-((E20)-(E37)) (C has the previous days bed available, and E20 has intakes, E37 has discharges) and the rows obviously change as it goes down the line of days. I am trying to create a rule that prevents me from adding any counts into our intake section that would make this formula in any of the cells go into the negatives. I want an error message to pop up that literally prevents me from adding a person that might make any of those formulas going forward go into the negatives to make sure we never overbook. I tried adding a data violation for that row to show an error message for anything less that 0 but it didn't do anything because I then tried to force an error on a random day and the numbers just showed negative going forward. I would love some help on this!

 

[6StringJazzer]

You showed a formula, but data validation has to be applied to cells where you enter data. Where are you entering data? Can you show us a table or at least a screen shot of what your sheet layout looks like? I followed most of your explanation but got lost on the last line where you explained why your data validation didn't do anything.

BTW that formula has superfluous parentheses. It could be written as

=C38 - (E20 - E37)

and even further simplified to

=C38 - E20 + E37

 

[sf1221]

Here is a screen shot. the count column has numbers that are then summed into the beds available with the formula I posted. It makes sense that data validation doesn't work because they are all formulas in this row (except the blanks), so is there another way to make sure the entire "beds available" row doesn't allow you to enter a count into the intakes that then causes the "beds available" for each day to go into the negatives? for example, a "beds available" cell for a certain day may say 5 so I go ahead a add an intake however if I already have 5 intakes on a future day this is then an error because it will cause that future day to go into the negatives causing an overbook. I have been able to set a conditional format so at lease those cells turn red (I am attaching another screenshot of what that looks like) however I would really like to get an actual error message preventing me from even entering a count into a day if it causes negatives on a future day because sometimes it might ne a day that is farther ahead than I see on the screen.