# Folrmula that retuns last cell in a column

#### ausnewexcel

##### New Member
I have a number worksheets to which data is added to the next free cell in columns as time moves on.

On the summary page I would like to insert a formula that returns the contents of the last cell before a blank cell from the columns so that is shows the latest entry.

Is there a standard formula without having to use VBA that can be used?

Thanks

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Banker1

##### Active Member
I'm sure someone will come up with a more unique answer but here is what I would do:

In Column A I would create numbers from 1 - XXX assuming the 1 is in A1, with the formula
Code:
``=IF(ISBLANK(B2),"",A1+1)``
in A2 copied down as far as you need. This will create a number for each row which has data and a blank if there is no data in the row.

On your summary sheet I would designate the last column from your data sheet by using
Code:
``=MAX(DATASHEET!\$A:\$A``
and use a VLOOKUP (i.e., VLOOKUP(A1,DATASHEET!\$A:\$D,4,0)) to gather what ever data you need based on the number returned from MAX.

#### stanleydgromjr

##### Banned
ausnewexcel,

I hope this helps.

Excel Workbook
ABCDE
1Title ATitle BTitle C
2222
3333
44
55
66
7
Sheet1

Excel Workbook
ABCDEF
1Title ATitle BTitle CTitle DTitle E
2456
3
Summary

Have a great day,
Stan

#### mikerickson

##### MrExcel MVP
If there are no blank cells in the column,
=INDEX(A:A,COUNTA(A:A),1) will return the last entry in column A.

If all of column A's blank cells are A5 or above
=INDEX(A:A,COUNTA(A:A)+COUNTIF(A1:A5,"="),1)
will return the last entry in column A.

Replies
9
Views
79
Replies
3
Views
75
Replies
1
Views
79
Replies
2
Views
294
Replies
7
Views
243

1,191,691
Messages
5,988,111
Members
440,126
Latest member
duque00

### 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.

### Which adblocker are you using?

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

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