Looking for an excel function which can mirror opposite the data

[wilchong via OfficeKB.com]

Dear sir,
There are 2 data sets is assumed running in A1 down, viz.:
From A1 down to A10, are showing a set of number: from 1 to 10
In the cell from B1 to B10, are showing from 10 to 1, which is decreasing
from 10 to 1.

My question: I want to know is there any excel formula which I can place in
B1 and pick A10's number; B2 pick A9's number and etc.

I know there is an easy way to do it, just type "=A10" in the cell of B1, but
it is quite difficult to do it when I have more than 200 number, e.g. A1 to
A200.

Thanks for your advice,
Wilchong

 

[T. Valko]

Try this:

Entered in B1 and copied down to B1.

=INDEX(A$1:A$10,ROWS(A1:A$10))

 

[AltaEgo]

If you have a static number of rows:

=INDIRECT("A"&11-(ROW()))

Note that the 11 in the above is the number equal to the total number of
filled rows plus 1. In other words, the above works when values in A1 to A10
need to be inverted.


If the number of rows is dynamic (i.e. the total number of rows changes, a
solution would be to set up a dynamic range in column A and use in
conjunction with the formula below..

The following formula should enable you to invert the values in column A,
irrespective whether blank or filled

=INDIRECT("A" &COUNTA(myRange)+COUNTBLANK(myRange) +1 - ROW())

Note that any blanks in column A will show a zero value in column B.


How to set up a dynamic named range:
http://www.contextures.com/xlNames01.html#Dynamic

 

[Matt O]

YES! Was looking for the same thing. Biff's answer nailed it. Thank you!