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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

Try this:

Entered in B1 and copied down to B1.

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

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:93d5a2390f1a2@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

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



--
Steve

"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:93d5a2390f1a2@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

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

On Sunday, March 29, 2009 10:33 PM wilchong via OfficeKB.com wrote:

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1


On Sunday, March 29, 2009 11:39 PM T. Valko wrote:

Try this:

Entered in B1 and copied down to B1.

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

--
Biff
Microsoft Excel MVP


On Tuesday, March 31, 2009 11:04 PM AltaEgo wrote:

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



--
Steve

"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:93d5a2390f1a2@uwe...