Microsoft Office Tutorials and References
In Depth Information
Figure 15-13: A multicell array formula displays the entries in A4:A13 in reverse order.
Sorting a range of values dynamically
Figure 15-14 shows a data entry range in column A (named Data ). As the user enters values into that range, the
values are displayed sorted from largest to smallest in column C. The array formula in column C is rather
simple:
{=LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data))))}
If you prefer to avoid the #NUM! error display, the formula gets a bit more complex:
{=IF(ISERR(LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data))))),
“”,LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data)))))}
If you require compatibility with versions prior to Excel 2007, the formula gets a bit more complex:
{=IF(ISERR(LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data))))),””,LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data)))))}
Note that this formula works only with values. The file at this book's website has a similar array formula ex-
ample that works only with text.
Search JabSto ::




Custom Search