Question: How do I invert / reverse a list using named ranges? My list has a named range Some_list (D3:D7), see picture below.

inverted-list-using-cell-references21

Answer: See picture below.

Formula in A4:A33:

=IF(ROWS(Some_list)-ROW()+ROW(Invert_list_start)>=1, INDEX(Some_list, ROWS(Some_list)-ROW()+ROW(Invert_list_start)), "") + CTRL + SHIFT + ENTER

inverted-list-using-defined-names

Named ranges
Invert_list_start (A4)
Some_list (D3:D7)
What is named ranges?

How to change a named range
Click on "Name manager" on tab "Formulas" in excel 2007. Then change named ranges.

Download excel example file
invert-a-list-using-named-ranges.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

ROWS(array) returns the number of rows in a reference or an array

Related posts:

Invert a list using cell references in excel

Excel: How to invert a list

Reverse a list ignoring blanks in excel

Excel charts: Multiple series and named ranges

Quickly create links to sheets, tables, pivot tables and named ranges in a workbook