How can I find the last populated cell in a column

M

mladew

I need to automatically read the last populated cell in a given column.


Example:Cell R4 will pick up & display the last item in column B.

There are a list of items in column B, ranging from cell B5 to B16.

B16 is $200.00, then R4 should read $200.00 as well.

If column B gets added on and B17 is $275.00 then R4 shoul
automatically read $275.00.

This should hold true for text or numbers
 
F

Frank Kabel

Hi
find below a couple of possible formulas (depending on the type of your
data): In your case take A.2.d or A.2.e
-------------------
A. Collection of formulas to return the last value in a COLUMN
depending on the type/structure of data.
1. If you have no blank rows in between use
=OFFSET($A$1,COUNTA($A:$A)-1,0)

2. If you have blank rows in between try the following depending of the
type of values in your column:
2.a. If you have ONLY text values in column A try
=INDEX(A:A,MATCH(REPT("z",255),A:A))

2.b. If you have ONLY numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))
or
=LOOKUP(9.99999999999999E307,A:A)

2.c. If you have BOTH types (text and values), but AT LEAST one text
and one numeric entry
=INDEX(A:A,MAX(MATCH(9.99999999999999E307,A:A),MATCH(REPT("z",255),A:A)
))

2.d. If you don't know the type of data use the following array
function (entered with CTRL+SHIFT+ENTER)
=INDEX(A:A,MAX(IF(ISBLANK(A:A),0,ROW(A:A))))

2.e If you don't want to count formula results like ="" as entry adapt
2.d. as follows:
=INDEX(A:A,MAX(IF(A:A<>"",0,ROW(A:A))))

----------

B. Collection of formulas to return the last value in a ROW depending
on
the type/structure of data.
1. If you have no blank columns in between use
=OFFSET($A$1,0,COUNTA($1:$1)-1)

2. If you have blank columns in between try the following depending of
the type of values in your row:
2.a. If you have ONLY text values in column A try
=INDEX(1:1,1,MATCH(REPT("z",255),1:1))

2.b. If you have ONLY numbers in column A:
=INDEX(1:1,1,MATCH(9.99999999999999E307,1:1))
or
=LOOKUP(9.99999999999999E307,1:1)

2.c. If you have BOTH types (text and values), but AT LEAST one text
and one numeric entry
=INDEX(1:1,1,MAX(MATCH(9.99999999999999E307,1:1),MATCH(REPT("z",255),1:
1)))

2.d. If you don't know the type of data use the following array
function (entered with CTRL+SHIFT+ENTER)
=INDEX(1:1,1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))))

2.e If you don't want to count formula results like ="" as entry adapt
2.d. as follows:
=INDEX(1:1,1,MAX(IF(1:1<>"",0,COLUMN(1:1))))
 
M

mladew

I did not have blank rows in the column, so example A.1 worked.

Thanks again for your help. Your example was easy to adapt to m
specific use
 
B

Biff

Nice of you to "cover all the bases".

Biff
-----Original Message-----
Hi
find below a couple of possible formulas (depending on the type of your
data): In your case take A.2.d or A.2.e
-------------------
A. Collection of formulas to return the last value in a COLUMN
depending on the type/structure of data.
1. If you have no blank rows in between use
=OFFSET($A$1,COUNTA($A:$A)-1,0)

2. If you have blank rows in between try the following depending of the
type of values in your column:
2.a. If you have ONLY text values in column A try
=INDEX(A:A,MATCH(REPT("z",255),A:A))

2.b. If you have ONLY numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E307,A:A))
or
=LOOKUP(9.99999999999999E307,A:A)

2.c. If you have BOTH types (text and values), but AT LEAST one text
and one numeric entry
=INDEX(A:A,MAX(MATCH(9.99999999999999E307,A:A),MATCH(REPT ("z",255),A:A)
))

2.d. If you don't know the type of data use the following array
function (entered with CTRL+SHIFT+ENTER)
=INDEX(A:A,MAX(IF(ISBLANK(A:A),0,ROW(A:A))))

2.e If you don't want to count formula results like ="" as entry adapt
2.d. as follows:
=INDEX(A:A,MAX(IF(A:A<>"",0,ROW(A:A))))

----------

B. Collection of formulas to return the last value in a ROW depending
on
the type/structure of data.
1. If you have no blank columns in between use
=OFFSET($A$1,0,COUNTA($1:$1)-1)

2. If you have blank columns in between try the following depending of
the type of values in your row:
2.a. If you have ONLY text values in column A try
=INDEX(1:1,1,MATCH(REPT("z",255),1:1))

2.b. If you have ONLY numbers in column A:
=INDEX(1:1,1,MATCH(9.99999999999999E307,1:1))
or
=LOOKUP(9.99999999999999E307,1:1)

2.c. If you have BOTH types (text and values), but AT LEAST one text
and one numeric entry
=INDEX(1:1,1,MAX(MATCH(9.99999999999999E307,1:1),MATCH (REPT("z",255),1:
1)))

2.d. If you don't know the type of data use the following array
function (entered with CTRL+SHIFT+ENTER)
=INDEX(1:1,1,MAX(IF(ISBLANK(1:1),0,COLUMN(1:1))))

2.e If you don't want to count formula results like ="" as entry adapt
2.d. as follows:
=INDEX(1:1,1,MAX(IF(1:1<>"",0,COLUMN(1:1))))


--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
Top