Reversing a Dynamic Drop-Down List in Data Validation..

**e4excel** · 12-15-2008, 12:54 AM

Reversing a Dynamic Drop-Down List in Data Validation..I always use the OFFSET function to get a Dynamic List of Items in the regular Drop-down box..But I have a requirement where I need to use the Last Entry First as these options would be the most recent ones..So i have to keep them for Selection on the top..Regular Dynamic Validation formula[ =OFFSET($B$2,0,0,COUNTA($B:$B)-1,1) ]Revering the List..[ =OFFSET($B$2,COUNTA($B:$B)-ROW(),0) ]The only problem is to always reserve a Helper Column to store the Reversed List and then the same can be used in Data Validation?I would be happy if someone could help me by-pass this helper column and help me with a Dynamic as well as Reverse List..!

**dominicb** · 12-15-2008, 06:24 AM

Good morning e4excel

Originally Posted by e4excel

I always use the OFFSET function to get a Dynamic List of Items in the regular Drop-down box..But I have a requirement where I need to use the Last Entry First as these options would be the most recent ones..

There is no built in method to reverse the list in a data validation dropdown. The ideal method method would be to use a combobox from the Control Toolbox, as this can be rearranged in any order you please, and also has a "jump to" facility, so as you type the appropriate entry is selected.

If you really need to use DV, the Debra Dalgleish has a workaround here that might help you.

HTH

DominicB

**e4excel** · 12-15-2008, 09:30 AM

Good Evening from my part of the World.But unfortuantely tha link does not have any example on Reversing the ListEx:123Dropdown should be321NOw this list not nly should be reversed but also Expanding...

**martindwilson** · 12-15-2008, 11:53 AM

try using named range instead.
say your validation list is in b1:b10 define a name for it
leave b1 empty then put your list in b2:b10
when you want to ad a new one
click on b2 /insert/shift cells down enter your new value
this will now show at top of list and the named range will increase to b1:b11
automatically

**e4excel** · 12-15-2008, 12:20 PM

Due to some issues with the formatting the requirement was not conveyed properly..

Lets say I have a list of numbers from 1 to 10 from A2:A11 then in Cell C2 I want 10 as the first option in the Dropdown

**martindwilson** · 12-15-2008, 12:31 PM

from the start put them in as 10,9,8.... down in cells a2:a11
then name range a1:a11 lets say "mylist"
in c2 data/validation/allow/list then enter
=mylist
and drop down in c2 will show blank followed bt 10,9,8......
to add new value at top say number 11
click on a2 insert/shift cells down and type 11 into a2
now if you look at the named range "mylist" you will see it has expanded to a1:a12
dropdown in c2 will now show blank followed by 11,10,9,8........
so from now on just insert at a2 and the latest value will be at top of list