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!

 

[Toadstool]

Looks like Fluff beat my OFFSET approach.

Book1
	A	B
1	FROM	TO
2	Week 1	Week 1
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Week_list!$A$1:$A$53
B2	List	=OFFSET(Week_list!$A$1,MATCH($A$2,Week_list!$A$1:$A$53,0)-1,,54-MATCH($A$2,Week_list!$A$1:$A$53,0))

 

[Martin_H]

I am getting same problem.

 

[Fluff]

Did you change Main in the formula to match the sheet name with the DV?
Also did you call the Defined name Weeks?

 

[Martin_H]

Did you change Main in the formula to match the sheet name with the DV?
Also did you call the Defined name Weeks?

I have renamed my sheet to Main to met your criteria and I also used name Weeks for name manager.

 

[Fluff]

Did you have a week selected in A2 when you tried to create the DV in B2?

 

[Martin_H]

Yes I had.

 

[Fluff]

Not sure what's going on then.
On the sheet with the DV out this in a blank cell

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

What does it return?

 

[Martin_H]

I am sorry Fluff, it was my bad. In the week_list I have been using format "1 WEEK" instead "WEEK 1".

Working perfectly as a charm now. Thank you for your time.

God bless you mate. I appreciate it.

 

[Fluff]

Glad you sorted it & thanks for the feedback.