Folrmula that retuns last cell in a column

[ausnewexcel]

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

 

[Banker1]

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

=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

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

Obviously you have to adjust my suggestion to fit your need.

 

[stanleydgromjr]

ausnewexcel,

I hope this helps.

Excel Workbook
	A	B	C	D	E
1	Title A	Title B	Title C
2	2	2	2
3	3	3	3
4	4
5		5
6			6
7
Sheet1

Excel Workbook
	A	B	C	D	E	F
1	Title A	Title B	Title C	Title D	Title E
2	4	5	6
3
Summary

Have a great day,
Stan

 

[Banker1]

Would look something like this:

Book1
	A	B	C	D	E	F	G
2	Row	DataA	DataB	DataC		Summary	Value
3	1	10	A	15		10	150
4	2	20	B	30
5	3	30	C	45
6	4	40	D	60
7	5	50	E	75
8	6	60	F	90
9	7	70	G	105
10	8	80	H	120
11	9	90	I	135
12	10	100	J	150
Sheet1

 

[mikerickson]

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.