If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I find the last populated cell in a column
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 should automatically read $275.00. This should hold true for text or numbers. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
How can I find the last populated cell in a column
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),MAT CH(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),M ATCH(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 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 should automatically read $275.00. This should hold true for text or numbers. --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
How can I find the last populated cell in a column
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 my specific use. --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
How can I find the last populated cell in a column
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),MA TCH(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 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 should automatically read $275.00. This should hold true for text or numbers. --- Message posted from http://www.ExcelForum.com/ . |
Thread Tools | |
Display Modes | |
|
|