Finding Last Cell's Address Using Formula

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Thanks you so much Aladin The Great Excel Formula Expert. Thanks again

GNaga
 

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,124
Office Version
  1. 365
Platform
  1. Windows
Aladin Akyurek;93987 The above is applicable only to columns of numeric type.[/QUOTE said:
What is the formula for an Alphanumeric column

M.
 

Forum statistics

Threads
1,143,733
Messages
5,720,553
Members
422,291
Latest member
Deveshk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top