Last populated cell in a column
I am trying to write a formula that will recognize the last populated cell in
a given column, and then display what is in that cell. Is there a function that will recognize the last populated cell in a given column? -- RedFive |
Last populated cell in a column
Lots of options here...
http://www.xldynamic.com/source/xld.LastValue.html -- HTH... Jim Thomlinson "RedFive" wrote: I am trying to write a formula that will recognize the last populated cell in a given column, and then display what is in that cell. Is there a function that will recognize the last populated cell in a given column? -- RedFive |
Last populated cell in a column
Hi,
=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000<"")))) or if you know its a number =LOOKUP(10^23,A:A) or if you know its text =MATCH(REPT("z",255),A:A) Mike "RedFive" wrote: I am trying to write a formula that will recognize the last populated cell in a given column, and then display what is in that cell. Is there a function that will recognize the last populated cell in a given column? -- RedFive |
Last populated cell in a column
Thanks, worked just like I wanted it to
-- RedFive "Mike H" wrote: Hi, =INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000<"")))) or if you know its a number =LOOKUP(10^23,A:A) or if you know its text =MATCH(REPT("z",255),A:A) Mike "RedFive" wrote: I am trying to write a formula that will recognize the last populated cell in a given column, and then display what is in that cell. Is there a function that will recognize the last populated cell in a given column? -- RedFive |
Last populated cell in a column
Your welcome and glad I could help
Mike "RedFive" wrote: Thanks, worked just like I wanted it to -- RedFive "Mike H" wrote: Hi, =INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000<"")))) or if you know its a number =LOOKUP(10^23,A:A) or if you know its text =MATCH(REPT("z",255),A:A) Mike "RedFive" wrote: I am trying to write a formula that will recognize the last populated cell in a given column, and then display what is in that cell. Is there a function that will recognize the last populated cell in a given column? -- RedFive |
Last populated cell in a column
=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000<""))))
A little bit shorter... =LOOKUP(2,1/(A1:A65535<""),A1:A65535) -- Rick (MVP - Excel) |
Last populated cell in a column
Rick,
I've posted that a dozen time for something like =LOOKUP(2,1/(A1:A65535="This"),B1:B65535) But never considered it for finding the last value in the same column Nice one. But it can be made even shorter =LOOKUP(2,1/(A1:A65535<""),A:A) Mike "Rick Rothstein" wrote: =INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000<"")))) A little bit shorter... =LOOKUP(2,1/(A1:A65535<""),A1:A65535) -- Rick (MVP - Excel) |
Last populated cell in a column
I'm glad you posted that back to me. As it turns out, I have both versions
of that formula in my "library" for some reason. I know in the past I used the version you posted; but for some reason I grabbed the other one this time. I have now updated my "library" so that only the one version of the formula now exists in it. Thanks. -- Rick (MVP - Excel) "Mike H" wrote in message ... Rick, I've posted that a dozen time for something like =LOOKUP(2,1/(A1:A65535="This"),B1:B65535) But never considered it for finding the last value in the same column Nice one. But it can be made even shorter =LOOKUP(2,1/(A1:A65535<""),A:A) Mike "Rick Rothstein" wrote: =INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A 1000<"")))) A little bit shorter... =LOOKUP(2,1/(A1:A65535<""),A1:A65535) -- Rick (MVP - Excel) |
All times are GMT +1. The time now is 12:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com