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]

Hi Tye443,

Yes, you could use a constructed LoV like this:

Tyr443.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Student	Select Course					Courses	Attendees		Constructed LoV	3
2	Fred	Latin					English	0		English
3	Bert	German					French	1		French
4	Sue	Latin					Latin	3		German
5	Jim	Latin					German	1
6	Sally	French						0
7								0
8								0
Sheet1

Cell Formulas
Range	Formula
I2:I8	I2	=COUNTIF($C$2:$C$999,H2)
K2:K8	K2	=IFERROR(INDEX($H$2:$H$99,AGGREGATE(15,6,ROW($H$2:$H$99)-ROW($H$1)/(($I$2:$I$99<$L$1)*($H$2:$H$99<>"")),ROW()-ROW($K$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
C2:C6	List	=OFFSET($K$2,,,COUNTIF($K$2:$K$99,"> "))

 

[tyr443]

Hi Tye443,

Yes, you could use a constructed LoV like this:

Tyr443.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Student	Select Course					Courses	Attendees		Constructed LoV	3
2	Fred	Latin					English	0		English
3	Bert	German					French	1		French
4	Sue	Latin					Latin	3		German
5	Jim	Latin					German	1
6	Sally	French						0
7								0
8								0
Sheet1

Cell Formulas
Range	Formula
I2:I8	I2	=COUNTIF($C$2:$C$999,H2)
K2:K8	K2	=IFERROR(INDEX($H$2:$H$99,AGGREGATE(15,6,ROW($H$2:$H$99)-ROW($H$1)/(($I$2:$I$99<$L$1)*($H$2:$H$99<>"")),ROW()-ROW($K$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
C2:C6	List	=OFFSET($K$2,,,COUNTIF($K$2:$K$99,"> "))

Hi Toadstool,
Thanks for the awesome reply, it does most of what I want, however I have a question I hope you can help with, after experiementing I can't seem to find a solution.
In L1 you have the number representing the maximum number of people per course, how would I go about doing it if each course had it's own maximum number in the cell next to it?
I have tried it a few ways, but the best I get is an empty slot in my dropdown menu, which isn't great.
Thank you very much for your help so far, it has led to me learning a lot of new things in excel.

 

[abdelfattah]

@Toadstool nice formula but I'm asking if I would show all in column k whatever the value in column I mean if it's duplicated in column c many time then it should show in column k without duplicated but show all of them

 

[Toadstool]

In L1 you have the number representing the maximum number of people per course, how would I go about doing it if each course had it's own maximum number in the cell next to it?

With a little finagling I've changed the formula.

Tyr443.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Student	Select Course					Courses	Max Attendees	Slots Available		Constructed LoV
2	Fred	French					English	2	0		Latin
3	Bert	English					French	1	0		German
4	Sue	English					Latin	3	2
5	Jim	Latin					German	4	3
6	Sally	German							0
7									0
2ndresp

Cell Formulas
Range	Formula
J2:J7	J2	=I2-COUNTIF($C$2:$C$999,H2)
L2:L7	L2	=IFERROR(INDEX($H$2:$H$99,AGGREGATE(15,6,ROW($H$2:$H$99)-ROW($H$1)/(($J$2:$J$99>0)*($H$2:$H$99<>"")),ROW()-ROW($L$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
C2:C6	List	=OFFSET($L$2,,,COUNTIF($L$2:$L$99,"> "))

 

[Toadstool]

@Toadstool nice formula but I'm asking if I would show all in column k whatever the value in column I mean if it's duplicated in column c many time then it should show in column k without duplicated but show all of them

So you don't want to limit attendees? Then it doesn't need the constructed LoV, just use the Data Validation OFFSET point directly at your list of courses.

Tyr443.xlsx
	B	C	D	E	F	G	H	I
1	Student	Select Course					Courses	Attendees
2	Fred	English					English	2
3	Bert	French					French	2
4	Sue	English					Latin	0
5	Jim	French					German	1
6	Sally	German						0
7								0
Abdelfattah

Cell Formulas
Range	Formula
I2:I7	I2	=COUNTIF($C$2:$C$999,H2)

Cells with Data Validation
Cell	Allow	Criteria
C2:C7	List	=OFFSET($H$2:$H$99,,,COUNTIF($H$2:$H$99,"> "))

 

[abdelfattah]

@Toadstool awesome formula that what I want many thanks

 

[tyr443]

With a little finagling I've changed the formula.

Tyr443.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Student	Select Course					Courses	Max Attendees	Slots Available		Constructed LoV
2	Fred	French					English	2	0		Latin
3	Bert	English					French	1	0		German
4	Sue	English					Latin	3	2
5	Jim	Latin					German	4	3
6	Sally	German							0
7									0
2ndresp

Cell Formulas
Range	Formula
J2:J7	J2	=I2-COUNTIF($C$2:$C$999,H2)
L2:L7	L2	=IFERROR(INDEX($H$2:$H$99,AGGREGATE(15,6,ROW($H$2:$H$99)-ROW($H$1)/(($J$2:$J$99>0)*($H$2:$H$99<>"")),ROW()-ROW($L$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
C2:C6	List	=OFFSET($L$2,,,COUNTIF($L$2:$L$99,"> "))

Hey Toadstool,

Its great, does exatly what I needed. Although I have a small issue. When one of the values is "removed" and changed into the next value, I then get two of the same value, I don't know how I have managed to do that, any ideas?

So for example if the max attendees was reached for latin and it gets changed to german, for some reason I still have German in the column below.

Sorry I should say that I edited your formula.

=IFERROR(INDEX($A$2:$A$1048576,AGGREGATE(15,6,ROW($A$2:$A$1048576)-ROW($A$1)/(($S2<$E2)*(A$2:A$1048576<>"")),ROW()-ROW($X$1))),"")

where S# is the aggregate value so far, and E# is the maximum value S# can reach before being removed.

 

[Toadstool]

Tyr443,

I'm not following what you're saying?
You can't change the column H entries after you've started data entry as the previous values aren't changed and the constructed LoV doesn't check for duplicates.

Changing the range to the last means you can't insert any new rows and the formula is slowed down so I'd just pick a number which is above the maximum you'd expect to enter.

I don't know what you've got in columns S, E and X.

 

[tyr443]

Tyr443,

I'm not following what you're saying?
You can't change the column H entries after you've started data entry as the previous values aren't changed and the constructed LoV doesn't check for duplicates.

Changing the range to the last means you can't insert any new rows and the formula is slowed down so I'd just pick a number which is above the maximum you'd expect to enter.

I don't know what you've got in columns S, E and X.

Test.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
3	Maths	2	3	3	3	11	15		English
4	IT	1	0.5	0.5	0.5	2.5	5		Maths
5	Business	1	1	1	1	4	10		IT
6	Physics	2	2	2	2	8	10		Business
7	Chemistry	2	2	2	4	10	10
8	Biology	5	5	5	5	20	30		Chemistry
Sheet1

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

The aim is that I2 onwards contain a list of the courses not yet complete, with no exmpty cells, and no doubles of remaining courses. The list would need to update as extra months are added that will increase the "Hours to date" column.

In this case the list would show only: Maths, IT, Business, Physics and Biology.

This list would then be in a dropdown menu on another sheet, and as the "hours required" are met that dropdown list would have only options for courses in which the hours required are not yet reached.

Sorry I have only just made this version as an example, I realise it would have been far easier to do this before.