Data Validation Dropdown remove item when limit reached

[dave3009]

Hi Guys

I need a little hand with find a solution to a data validation issue. I checked the search and the usual help sites such as contextures.com but haven't managed to find an answer.

Basically I am creating a booking system, when a class has reached it's limit I want it not to appear in my validation dropdown. The limit for arguments sake is 15,

Excel Workbook
	A	B
1	Class	Attendees
2	Class 1	1
3	Class 2	15
4	Class 3	6
5	Class 4	2
6	Class 5	15
Sheet1

Excel 2007

At the moment I use a standard OFFSET so I can add and delete classes and keep my list neat, but for this example I do not want sheet users to be able to book in more than the limit to one particular class so when it reaches 15 it will no longer be available as a choice. So in the above example only classes 1, 3 & 4 would be options.

I'm going to be sharing this workbook if that makes any difference to the possible solutions. And restrictions at work mean VBA is not an option, apparently.

Thanks for looking, TIA for any solutions

Regards


Dave

 

[T. Valko]

Hi Biff

There will not be any duplicate classes to consider.

Thanks


Dave

Not sure how you're wanting to implement this but here's a small sample file that demonstrates it.

ZZZdave3009.xlsx 10kb

http://cjoint.com/?AHerDxdskbV

 

[dave3009]

Hi Biff

This was an excellent help, Thank you very much

Regards


Dave

 

[T. Valko]

Hi Biff

This was an excellent help, Thank you very much

Regards


Dave

You're welcome. Thanks for the feedback!

 

[dave3009]

Hi Biff

I hope I can come back to this to expand a little. Sorry if I am being a pest.

Normally we run classes which can hold 15 people in total, but on occasion we need to sub divide the classes depending on the needs of the group being booked in. these would be split equally so on occasion we split a class of 15 into 3 classes of 5. It does not get any more complex than that.

Is there a way I can set this kind of set up or am I being too demanding?

TIA

Dave

 

[T. Valko]

Hi Biff

I hope I can come back to this to expand a little. Sorry if I am being a pest.

Normally we run classes which can hold 15 people in total, but on occasion we need to sub divide the classes depending on the needs of the group being booked in. these would be split equally so on occasion we split a class of 15 into 3 classes of 5. It does not get any more complex than that.

Is there a way I can set this kind of set up or am I being too demanding?

TIA

Dave

Can you refresh my memory with an example?