VBA/Formula to lock cells when criteria is met

[drewhx15]

Hi All,

I hope that you can help me with this one.

I hope I explain this well.

OK so I am trying to make a spreadsheet that has 5 weeks, each week has Monday - Friday. OK so fairly straight forward. There are 4 available times: 08:00, 09:00, 17:00 & 18:30 (These have been added as a list via Data Validation).

08:00 has 4 places available, when 4 users select this time slot it cannot be selected throughout the rest of the week and ideally if someone tries to select 08:00 it would display a message or it would be greyed out and prevents the user from selecting that time.

Basically it counts the amount of 08:00 and deducts it from the 4 that are available and when the 4 are used, the 08:00 cannot be selected in that day of the week.

EMPLOYEE	Day		Week 1		Week 2		Week 3		Week 4		Week 5
John Doe	Monday		09:00
	Tuesday		09:00
	Wednesday		09:00
	Thursday		09:00
	Friday		09:00

 

[mabbutt]

Your data validation should have this formula...

=IF(COUNTIF(F5:F8,">2"),Reduced_Times,All_Times)

Add two named ranges as Reduced_Times = 9:00,17:00,18:30 and All_Times = 8:00,9:00,17:00,18:30

Add a control cell to workout which times have been entered e.g.

=COUNTIF(F2:F5,"8:00")

The data validation points towards the control cell and picks the named range depending on the value.

You can remove the control cell and add the count if to the data validation formula but my skills are not that advanced!