Dynamic dropdown list

[Martin_H]

Hi,

I have 2 dropdown lists in cell A2 and B2. Both dropdown lists shows week numbers from 1 to 53. Column A is a starting point (FROM) and Column B is a ending point (TO).

Dropdown list in A2 will always shows all weeks (from 1 to 53) but dropdown list in B2 will only show weeks that are greater or equal to current A2 selection.

For example, if I choose WEEK 10 in dropdown list A2 I want to see only WEEK 10 and greater in dropdown list B2.

Is is possible?


Thank you!

 

[Fluff]

How are you populating the DV in col A?

 

[Martin_H]

All week numbers are written on another sheet and I am using this sheet as a source for both dropdown lists.

 

[Fluff]

What is the name of the sheet & what cells are the week numbers in?

 

[Martin_H]

sheet is called Week_list and week numbers are in the cells A1:A53.

 

[Fluff]

Ok, cant remember if this works with 2016.
Open the Name & create a new name & in the refers to box put

=INDEX(INDEX(Week_List!$A$1:$A$53,MATCH(Main!$A2,Week_List!$A$1:$A$53,0)):Week_List!$A$53,)

Change Main! to the name of the worksheet with the drop down.
Then in the DV on B2 for the source use

=Weeks

or whatever name you used.

 

[Martin_H]

Thank you for your time.

I am getting this "The Source currently evaluates to an error. Do you want to continue?" after inserting DV with =weeks.

 

[Fluff]

If you select a week in A2 , does the DV in B2 work & give you a list of weeks?

 

[Martin_H]

No it doesnt.

 

[Fluff]

Ok, sounds as though that doesn't work with 2016, change the formula in the name to

=OFFSET(Week_List!$A$1,MATCH(Main!$A2,Week_List!$A$1:$A$53,0)-1,,COUNTA(Week_List!$A$1:$A$53)-MATCH(Main!$A2,Week_List!$A$1:$A$53,0)+1)