exceldropdown

How to use drop down list with range of dates (years) by specific year in Excel

I want to create conditional drop down list when user fill the year in column A and in column B is filled the range which is taken from list defined in column E. How can I do it?

Range of years list

Solution

  • Here is a solution which requires a few helper columns

    https://1drv.ms/x/s!AncAhUkdErOkgqRfHIC5Alfyiof2VQ?e=5RehGh

    =NUMBERVALUE(LEFT(J3;4))
=NUMBERVALUE(RIGHT(J3;4))
=L3-K3
=NUMBERVALUE(K3)
=IF(($N3+COLUMN()-COLUMN($M3)-1)<=$L3;$N3+COLUMN()-COLUMN($M3)-1;0)


=IF(COUNTIF($N$3:$Z$17;A4)=1;A4;"")
=OFFSET($C$4;;;COUNTIF(C4:$C$1000;">1");1)
=INDEX($J$3:$L$8;MATCH(E3;$K$3:$K$8;1);1)

    Replace semicolon with komma if your local Excel version needs it.

    You could combine some formulas to have less helper columns.

    enter image description here

    Chose from Drop Down menue

    enter image description here