Automatically updating dropdown menu

[tyr443]

I have a dropdown menu in multiple sheets that takes its content from another sheet with the name Courses.

Is it possible to remove options in a dropdown menu once a value is met in the "Courses" sheet, but by doing so only affect the tables after the criteria is met?

As an example, in the Sheet "Courses" there is the entry "English class" and once the number in the column next to "English class" reaches 20, I want the possiblity to select "English class" removed from the dropdown box for all subsequent sheets.

Sorry I can't provide any examples at the moment, I am currently only curious if it can be done, and how I would go about doing it.

 

[Toadstool]

At the end you want to use $I$1 because in the second row it will return ROW()-ROW($I$1) which is 2-1 which is 1, because we want the first row which matches the criteria.

You don't want ($F2<$G2) because each row of the AGGREGATE formula loks at every row from $A$2 to $A$99 so that check needs to be ($G$2:$G$99-$F$2:$F$99>0)

Tyr443.xlsx
	A	B	C	D	E	F	G	H	I
1	Course	Jan	Feb	Mar	Apr	Hours to date	Hours required		Contructed LoV
2	English	3	3	3	1	10	10		Maths
3	Maths	2	3	3	3	11	15		IT
4	IT	1	0.5	0.5	0.5	2.5	5		Business
5	Business	1	1	1	1	4	10		Physics
6	Physics	2	2	2	2	8	10		Biology
7	Chemistry	2	2	2	4	10	10
8	Biology	5	5	5	5	20	30
9
10
3rdResp

Cell Formulas
Range	Formula
F2:F8	F2	=SUM(B2:E2)
I2:I10	I2	=IFERROR(INDEX($A$2:$A$99,AGGREGATE(15,6,ROW($A$2:$A$99)-ROW($A$1)/(($G$2:$G$99-$F$2:$F$99>0)*($A$2:$A$99<>"")),ROW()-ROW($I$1))),"")

 

[tyr443]

Toadstool, you are a wizard! Thanks for your patience and help.

 

[tyr443]

I have a new part to the question, that I hope is possible.
If one of the hours required (G column) was text instead of a number then is there a way to also include it in the list.
So for example if the hours required for English was To be decided it would need to stay in the list until there is a decision on the rquired hours.

I had a go at altering

($G$2:$G$99-$F$2:$F$99>0)

with OR but I just can't seem to get it working. I think I need to sit down and take some time to understand what is there so far before I can edit it comfortably.

Also sorry to keep bugging you with more, this is most definitely the last time.

 

[Toadstool]

Sometimes it's just best to redesign your approach. Here I'd say use 999 to force its appearance or add a work column to populate same.