Value of last non-blank cell in a column
 

Frank,

I found your solution when I had the same problem of needing the formula to
only select the last non-blank entry. I am curious however in the "OFFSET"
solution which did not work for me (probably because I entered it
incorrectly, since the VLOOKUP did, thanks). The COUNTA(A:A) reference is
likely for the range in question, but what does the $A$1 refer to?

Thanks,
Kem

"Frank Kabel" wrote:

Hi
if you have no blank lines in between use:
=OFFSET($A$1,COUNTA(A:A)-1,0)

If you could have blank rows in between try:
=LOOKUP(2,1/(A1:A1000<""),A1:A1000)

-----Original Message-----
Hi,
I have a spreadsheet that has 52 rows in a particular
column, one row
for each week of the year. As the year progresses the
column fills up
with data but in the 53rd row of that column I want see
the value of
the last cell in that column that is not blank. Can
anyone assist
please?
Many thanks
ArtySin
.

A1 is the first cell offset will start from, so what it basically says is
start at A1, count how many cells are not emty in column A, offset with
that number - 1, so if A1:A8 has contents, offset will retrun what's in cell
A9 -1 which is A8

Unfortunately Frank Kabel is not with us anymore, he passed away in an
accident in early January this year

--

Regards,

Peo Sjoblom

"KemS" wrote in message
...
Frank,

I found your solution when I had the same problem of needing the formula
to
only select the last non-blank entry. I am curious however in the
"OFFSET"
solution which did not work for me (probably because I entered it
incorrectly, since the VLOOKUP did, thanks). The COUNTA(A:A) reference is
likely for the range in question, but what does the $A$1 refer to?

Thanks,
Kem

"Frank Kabel" wrote:

Hi
if you have no blank lines in between use:
=OFFSET($A$1,COUNTA(A:A)-1,0)

If you could have blank rows in between try:
=LOOKUP(2,1/(A1:A1000<""),A1:A1000)

-----Original Message-----
Hi,
I have a spreadsheet that has 52 rows in a particular
column, one row
for each week of the year. As the year progresses the
column fills up
with data but in the 53rd row of that column I want see
the value of
the last cell in that column that is not blank. Can
anyone assist
please?
Many thanks
ArtySin
.

Peo,
I am very sorry to hear about Frank. Thank you for letting me know.
Could I impose upon you further regarding this thread? Normally I can parse
out a formula I find in the groups and figure them out. But the formula
=LOOKUP(2,1/(A1:A1000<""),A1:A1000)
has me puzzled. The syntax from MS didn't even help.
But the mystery is the 1/(A1:A1000)<"". I sought a formula that would
always return the last non-blank cell in a series of OLAP date entries (week
1,2.... This formula works and returns the last entry (after modifying the
vector to match the table). So now I am just seeking to understand why it
works? Also, why 2 as a value since the value being looked up isn't in fact
two, but any value in the last cell? Sorry about the long question. My
curiousity is getting the best of me.

Best regards,
Kem


"Peo Sjoblom" wrote:

A1 is the first cell offset will start from, so what it basically says is
start at A1, count how many cells are not emty in column A, offset with
that number - 1, so if A1:A8 has contents, offset will retrun what's in cell
A9 -1 which is A8

Unfortunately Frank Kabel is not with us anymore, he passed away in an
accident in early January this year

--

Regards,

Peo Sjoblom

"KemS" wrote in message
...
Frank,

I found your solution when I had the same problem of needing the formula
to
only select the last non-blank entry. I am curious however in the
"OFFSET"
solution which did not work for me (probably because I entered it
incorrectly, since the VLOOKUP did, thanks). The COUNTA(A:A) reference is
likely for the range in question, but what does the $A$1 refer to?

Thanks,
Kem

"Frank Kabel" wrote:

Hi
if you have no blank lines in between use:
=OFFSET($A$1,COUNTA(A:A)-1,0)

If you could have blank rows in between try:
=LOOKUP(2,1/(A1:A1000<""),A1:A1000)

-----Original Message-----
Hi,
I have a spreadsheet that has 52 rows in a particular
column, one row
for each week of the year. As the year progresses the
column fills up
with data but in the 53rd row of that column I want see
the value of
the last cell in that column that is not blank. Can
anyone assist
please?
Many thanks
ArtySin
.