Dropdown list with formula for months of the current year till date

Hello,

Is it possible to create a drop-down list with the help of formula which shows only the months till the date in that year.
For example when i click the drop-down box today (14th February), i should only have January & Februay as options. However if i click the box in July, it should all the months between Jan & July available for selection & if i click in December, all 12 months of the year should be available.

If it is possible, please let me know how to do it. Thanks in advance for your time.

Originally Posted by dflak

This can be done using a named dynamic range as the source for a list-style data validation.

Column B has "set up" information. Cell B2 is the current date, cell B3 is the current month and cell B3 is where the current month is found on the list D2:D13.

I defined a named dynamic range: Month_List =OFFSET(Sheet1!$D$2,0,0,Sheet1!$B$4,1)


The offset command has 5 parameters
- A start cell (D2)
- Number or rows to go down (0)
- Number of columns to go right (0)
- Number of rows to return (whatever the value in cell B4 is)
- Number of columns to return (1)

Here is more information on the offset command and named ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges

Cell B6 has a list validation =Month_List

Hello,

Thanks for the solution.
It works. However, there are many people working on the excel & for that reason, i don't want to maintain a list (someone else may delete it inadvertently).
Is there no possibility of achieving the result just with formulas/function in a single cell (the drop-down list cell itself).
Thanks again for your help.

Originally Posted by dflak

You can put the list on a hidden sheet and if that's not enough, protect it. .

I can't think of another built-in formula that could yield the result, but I am fairly certain it van be done with VBA. However, I like to avoid VB whenever I can.

Thank you very much. I also do not want to use VBA. I have put the lint in a separate sheet & hidden it. Thanks again for the help.