Finding Last Cell's Address Using Formula

[gnaga]

Hi,

Is it possible to find a last cell address (A1,A2..) using the formula?. I could get only the row number using MATCH Function.

Thanks for your help?

GNaga

 

[Aladin Akyurek]

On 2002-09-03 07:22, gnaga wrote:
Hi,

Is it possible to find a last cell address (A1,A2..) using the formula?. I could get only the row number using MATCH Function.

Thanks for your help?

GNaga

( 1.) Activate Insert|Name|Define.
( 2.) Enter BigNum in the Names in Workbook box.
( 3.) Enter the following in the Refers to box:

9.99999999999999E+307

( 4.) Click OK.

=ADDRESS(MATCH(BigNum,A:A),1)

will give you the address of the last cell in use in column A. The second arg of ADDRESS is the column number of A.

The above is applicable only to columns of numeric type.


EDIT: Incomplete Step 3 corrected.
This message was edited by Aladin Akyurek on 2002-09-03 08:50

 

[gnaga]

Aladin

I could not get it worked. Every time the named range of BigNum is changing on its own and if I refer that named range in any of the cell I am getting an error of #Name?

Thanks

GNaga

 

[Aladin Akyurek]

On 2002-09-03 08:37, gnaga wrote:
Aladin

I could not get it worked. Every time the named range of BigNum is changing on its own and if I refer that named range in any of the cell I am getting an error of #Name?

Thanks

GNaga

See my earlier edited reply: Step 3 was incomplete.

 

[gnaga]

Thanks Aladin

It works fine and returning the cell address perfectly. But sorry my first question was wrong. I want the data houses in last cell of a particular column not the cell address.

Sorry for the inconvenience.

Thanks

GNaga

 

[Aladin Akyurek]

On 2002-09-03 09:01, gnaga wrote:
Thanks Aladin

It works fine and returning the cell address perfectly. But sorry my first question was wrong. I want the data houses in last cell of a particular column not the cell address.

Sorry for the inconvenience.

Thanks

GNaga

=INDEX(A:A,MATCH(BigNum,A:A))

Also:

=LOOKUP(BigNum,A:A)

which is probably less expensive.
This message was edited by Aladin Akyurek on 2002-09-03 09:05

 

[gnaga]

Thanks you so much Aladin The Great Excel Formula Expert. Thanks again

GNaga

 

[MartinL]

What is the formula for an Alphanumeric column

M.