Last cell entry in column - No VBA please

PeterBrazel

New Member
Joined
May 9, 2002
Messages
35
Can somebody help me by indicating how I can locate the last cell in a column that contains a numerical value. Alternatively the last row of a database.

Thanks in advance
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi,

=MATCH(9.99999999999999E+307,A:A)

will tell you what cell the last value is in.

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A),1)

will return its value.

Paddy
 
Upvote 0
Not quite there. That formula seems to return the row number I guess I need the cell address.

I am trying to find the min and the max of a column in a database.

Thanks
 
Upvote 0
=ADDRESS(MATCH(9.99999999999999E+307,A:A),MATCH(9.99999999999999E+307,A:A),4,1)


Paddy
 
Upvote 0
Paddy,

This is what I tried doing

=MIN(Nikkei!$M$4,M&MATCH(9.99999999999999E+307,Nikkei!M:M))

It failed. Without the concatination it returned 23 which is the row without the column address.
 
Upvote 0
Paddy,

Sorry about this but something is wrong. To test it I placed various numbers in a new sheet in column A then in a cell in column B I placed your formula it returned A1 which was not the minimum number.

Also it is the value of the number I want.

Thanks again in advance.
 
Upvote 0
sorry - vital bit missing:

=ADDRESS(MATCH(MIN(A:A),A:A,0),1,4,1)

for the address.

If you just want the value, isn't:

=min(a:a)

all you need?

paddy
 
Upvote 0
Paddy,

Yes I thin MIN(A:A) will do but it must be the sheet reference that is causing the problem. I have multiple sheets and the the formula needs to go to a sheet called Nikkei and obtain this value from column M.

=MIN(NIKKEI!M:NIKKEI!M) does not work but the MIN function works OK within the current sheet.

Perhaps I need a break.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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
Back
Top