How to reverse the order of a Named Range in a dynamic Data Validation list?

[bpjod]

Sorry if this has been asked and answered before but I haven't had luck finding an answer with the search function.


I have a Table named Revenue. The first column is Week Ending. The first entry (A3) is 08-Jan-11. The next cell down (A4) is =A3+7 and so on to the bottom of the column. Every week I post new data by going to the last cell of the table, press tab and a new row is entered for me with the new Week Ending date automatically entered. I then enter all that week's sales data in the other columns.


I'm trying to create a Dashboard page. I'd like to be able to pick a date for which to present the data on my Dashboard. I'm doing this from a Data Validation list. What I've done is created a named range: DateRange=Revenue[Week Ending]. Then in the cell on my Dashboard for picking the date I've set up a Data Validation drop down list by selecting Allow: List and Source: =DateRange.


This works perfectly well, except the oldest dates are at the top of the list and the most recent dates are at the bottom. I'm mostly interested in looking at the most recent data and so I'd like the most recent date to be at the top of my dynamic Data Validation list rather than at the bottom to facilitate date picking. Is this possible? How do I go about doing this? I'm using Excel 2010.

 

[NARAYANK991]

Hi ,


The standard way to reverse your list would be to use the construct :


=LARGE(DateRange,ROW(DateRange)-MIN(ROW(DateRange))+1)


However , this does not seem to work , when a named range using this formula is defined , and the named range is used in Data Validation.


However , if you can use a helper column , where you use the following formula :


=INDEX(DateRange,ROWS(DateRange)-ROW()+MIN(ROW(DateRange)))


and define a named range Reversed_Dates , using the following formula :


=OFFSET(Helper_Column_Start_Cell,,,ROWS(DateRange))


then you can use the named range Reversed_Dates in Data Validation.


For example , if your helper column is M , and you start from cell M11 , then Helper_Column_Start_Cell will be $M$11.


Narayan

 

[bpjod]

Thanks a bunch NARAYANK991


I simply added a column adjacent to Week Ending called Reverse Date, used your formula:


=INDEX(DateRange,ROWS(DateRange)-ROW()+MIN(ROW(DateRange)))


to fill it with the dates in reverse order. Then I set up a new named range ReverseDate=Revenue[Reverse Date] and used that in my Data Validation dropdown. To clean up the Revenue table I just hid the Reverse Date column. Worked great!