Limit the total value to plug into a range of cells??

[Marq]

Using Excel 2007

I hope I can explain this properly because I'm having a hard time wrapping my brain around this:

I have a spreadsheet that I need to limit the total allowable value plugged into a range of cells.

In cell B39 I plug in a value of lets say "10"..this 10 represents the total number of workers allowed to me on a particular day....the value in B39 can change at anytime..somedays I may be allowed 15 workers...somedays only 6.....whatever the case my be, I am committed to whatever value is in B39.

The range of cells which capture the day the 10 workers will be allocated is range P7:R32.

Rows 7 thru 32 are individual jobs descriptions (in column A).....and I basically scroll down thru the jobs and pick which jobs I have scheduled for that day and allocate wokers to each job, not to exceed the total number of workers allocated to me in cell B39.

The thing is, as I allocate the workers thru the P7:R32 range, and I meet the total allowed, i need to make the spreadsheet no longer allow me to plug numbers into the range P7:R32....so when I meet the number of 10 and go to plug more numbers into the P7:R32 range, Excel will not allow it.

All this is to make sure I do not over allocate workers for that particular day...i used only small values for my example....im really dealing with over 100 workers and its easy to lose count. I have sum's working for me, but I really need a "fail safe" in the sheet because others will be using it in the future....adding their workers also....so to have the sheet no longer except numbers, a sort of "lock out on a cell range" would be the ideal set up.

Man, I just re-read this and I hope I'm clear what I'm shooting for.

Can someone please help me out?

Marq

 

[stilesbn]

You could use conditional formatting to highlight whenever a value becomes greater than 100 or so.

 

[Marq]

You could use conditional formatting to highlight whenever a value becomes greater than 100 or so.

thought of it and mesing with it right now...but would rather the lock out idea if at all possible

 

[Marq]

You could use conditional formatting to highlight whenever a value becomes greater than 100 or so.

How would I make the conditional format effect the RANGE of cells instead of just one cell?!?!? Right now, even though I highlight the range in question the formatting all effects individual cells.

This is very confusing to me

 

[rallcorn]

If I understand you correctly, you're moving a job or number of workers from column A to column P & counting them?

If your target # of workers is in B39, how about using this approach?
C39 formula that counts the number of jobs/workers
D39 formula that says =IF(C391<=B39,"","QUOTA REACHED")

Then put this VBA code in the WORKSHEET & as soon as you go over 100 a message box will pop up & let you know you've exceeded you target

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D39").Value = "QUOTA REACHED" Then
     MsgBox ("STOP!! OVER QUOTA!!")
End If
End Sub

 

[Marq]

You could use conditional formatting to highlight whenever a value becomes greater than 100 or so.

conditional formatting like u suggested will not get me where I want to be...i need some kind of "lock out" when I exceed the value of cell B39

 

[stilesbn]

Enter in another row and put an if statement that will mox out at the number you need.

So if B39 can't be greater than 100 then in C39 put:

=if(B39>100,100,B39)

 

[rallcorn]

message box pop up from macro code above would force you to respond