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

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..!

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...

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

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

Originally Posted by martindwilson

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

I cannot type them in a reverse order...as the entries are date based the most recent ones always go to the bottom or the last rows and therefore the design cannot be changed..

I am well aware of using Name Defines...

What I want is to avoid creating a reverse list and then use it for Data Validation

Without changing the format, would be happy if someone could help me by-pass this extra column for storing the reverse list and directly get a Dynamic as well as Reverse List..which also expands in one formula...

Hope this explanation helps.

I cannot type them in a reverse order...as the entries are date based the most recent ones always go to the bottom or the last rows and therefore the design cannot be changed..

why not?
cant see the problem
turn your existing list upsite down then carry on from there
why do new ones have to go at bottom?
you asked how to do it and that's how !!!!

Originally Posted by martindwilson

why not?
cant see the problem
turn your existing list upsite down then carry on from there
why do new ones have to go at bottom?
you asked how to do it and that's how !!!!

I would like to reiterate that the order cannot be changed as the most recent entries go in the Last Rows however for all practical purposes the Dropdown should have the data of the most recent and thus the requirement to reverse the list.