Show current selection in drop down menu

**sandy.beach** · 05-04-2009, 02:16 PM

Hi guys i am in assistance of your brilliant brains, i have a working drop down menu which when selected i can select my data range such as below:

18/03/2009
19/03/2009
20/03/2009
21/03/2009
22/03/2009
23/03/2009
24/03/2009
25/03/2009

I am doing this by using a validation rule and having the source point to:

=(Date_Range)

as a defined name.

Now this works perfectly, the first date on my list is the 18/03/2009, when i change the date to something like 24/04/2009 and change it again it show me the first one on the list (18/03/2009), what i would like is when i click the drop down menu to select a different date it will show me the current selected date on the menu, so in the above case i will be looking at 24/04/2009 on the drop down menu and not 18/03/2009.

Is this possible?

Thanks

**JBeaucaire** · 05-04-2009, 03:32 PM

That's how my drop-down validation lists from named ranges behave. Yours doesn't?

Post up a sample sheet showing the bad behavior.

**sandy.beach** · 05-04-2009, 03:50 PM

Unfortunatly not, how weird, i have attached a sample, am i doing something wrong?

Thanks for taking a look at this for me

**Chance2** · 05-04-2009, 04:20 PM

It sounds like he is looking for the drop down to act like a combo box.
i.e. When you select an item in the combo box, that item is highlighted in the combo list (so you have 1,2,3,4,5 and 3 is selected, then the next combo list will start at 3,4,5).

A combo box will do as you ask, but it converts dates to text/general so the date is not displayed. You could use a "text" conversion of your date for the combo list, but I don't know how that would affect the rest of your code.

**sandy.beach** · 05-04-2009, 04:57 PM

oh a text conversion is not too good, my formula's do not run

how odd

**JBeaucaire** · 05-04-2009, 04:59 PM

Actually, Sandy, you tricked YOURSELF. Validation lists USUALLY show you the spot you've currently got selected when you reopen the list again. That's because of the "matching" nature to the way the display is handled.

But you created a validation list of dates formatted as 1/1/2009 and then formatted the validation cell to display the chosen date as 1-Jan-09.

That's not the same thing.

Now, when I format your Date_Range with the same formatting, look how it behaves now....

**sandy.beach** · 05-04-2009, 05:15 PM

Thanks alot, it is all working now, how silly of me, such a stupid mistake, i had thought it was not an issue in the formatting of the dates since they are written the same lol.

Thanks for your help