Question: How do I invert / reverse a list using cell references? My list is in D3:D7, see picture below.

inverted-list-using-cell-references21

Answer: See picture below

Formula in A4:A8:

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

inverted-list-using-cell-references1

Named ranges
Invert_list_start (A4)
What is named ranges?

How to customize formula to your excel workbook
Change named range to the cell where your inverted list start cell is to be.

Download excel example file
invert-a-list-using-cell-references.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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Extract cell values in a range using a criterion in excel
  2. Create a list of all numbers or text in a column in excel
  3. Count matching cell values in two columns in excel