I am trying to get the last value in a column. I don't know what row that
will be in - as that can vary. How can I address the last filled in cell in
a specific column?

Thanks,
Cat

Cat

From Bob Phillips.......=LOOKUP(2,1/(A1:A65535<""),A1:A65535)


Gord Dibben MS Excel MVP

On Tue, 1 Aug 2006 16:46:33 -0500, "Cat Chowdy" <catchowdyathotmaildotcom
wrote:

I am trying to get the last value in a column. I don't know what row that
will be in - as that can vary. How can I address the last filled in cell in
a specific column?

Thanks,
Cat

Depending on what type of data is involved, some methods are better than
others.

See this for a definitive guide:

http://xldynamic.com/source/xld.LastValue.html

Biff

"Cat Chowdy" <catchowdyathotmaildotcom wrote in message
...
I am trying to get the last value in a column. I don't know what row that
will be in - as that can vary. How can I address the last filled in cell
in a specific column?

Thanks,
Cat

Thanks! It works great. But can you explain to me what is happening.
More specifically I don't understand 1/(A1:A65535<"").

Cat


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Cat

From Bob Phillips.......=LOOKUP(2,1/(A1:A65535<""),A1:A65535)


Gord Dibben MS Excel MVP

On Tue, 1 Aug 2006 16:46:33 -0500, "Cat Chowdy" <catchowdyathotmaildotcom
wrote:

I am trying to get the last value in a column. I don't know what row that
will be in - as that can vary. How can I address the last filled in cell
in
a specific column?

Thanks,
Cat

Biff has provided a link to Bob Phillips site.

Bob shows many diverse ways to return last values.

The particular one I posted is on that page.

Here is Bob's explanation...........

This formula uses LOOKUP in its vector syntax form, with the lookup value as the
first parameter, the lookup vector as second, and the result vector as the last
parameter

The most interesting part of this formula is the lookup vector (the 2nd
parameter). The formula element
1/(1-ISBLANK(A1:A65535))
in this example returns the following array
{1;1;1;1;1;1;1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;...;#DIV/0!}

that is, the ISBLANK function returns an array of TRUE (blank cell) or FALSE
(non-blank cell) values.

Subtracting this from 1 converts the array to an array of 0 (blank) or 1
(non-blank) values.

Dividing 1 by this array then returns an array of #DIV/0 (blank) or 1
(non-blank) values

The LOOKUP searches for the value '2' within the array (which now consists only
of '1' and #DIV/0 values). The LOOKUP will not find this value, so it matches
the last value that is less than or equal to lookup value. This is the last '1'
within the range which represents the last filled cell

This type of formula can be used for a lot of similar problems using the second
parameter to create a lookup vector consisting of either '1' or '#DIV/0' errors
by setting the Boolean expression accordingly.

Constraints:

In the column variation, the ISBLANK function and the result vector have to be
passed an explicit range, it does not work with a complete column

If the entire column / row is empty this formula will return an #N/A error as
the LOOKUP function finds no value that is smaller or equal to the lookup value
(the lookup vector consists only of #DIV/0 errors)

This formula is quite slow

This solution ignores error values. If the range contains, for example, #DIV/0
errors the formula will not highlight this fact, but will return the last
non-error value.


Gord

On Tue, 1 Aug 2006 17:33:27 -0500, "Cat Chowdy" <catchowdyathotmaildotcom
wrote:

Thanks! It works great. But can you explain to me what is happening.
More specifically I don't understand 1/(A1:A65535<"").

Cat


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Cat

From Bob Phillips.......=LOOKUP(2,1/(A1:A65535<""),A1:A65535)


Gord Dibben MS Excel MVP

On Tue, 1 Aug 2006 16:46:33 -0500, "Cat Chowdy" <catchowdyathotmaildotcom
wrote:

I am trying to get the last value in a column. I don't know what row that
will be in - as that can vary. How can I address the last filled in cell
in
a specific column?

Thanks,
Cat




Gord Dibben MS Excel MVP

Thanks much Gord and Biff. I have been programming for some time, but have
never done much in Excel before. I was not aware that it would return the
last "less than" match if it didn't find an exact match. Also, I would have
thought all the #DIV/0!'s would have produced an error.

Thanks!
Cat


"Biff" wrote in message
...
Depending on what type of data is involved, some methods are better than
others.

See this for a definitive guide:

http://xldynamic.com/source/xld.LastValue.html

Biff

"Cat Chowdy" <catchowdyathotmaildotcom wrote in message
...
I am trying to get the last value in a column. I don't know what row that
will be in - as that can vary. How can I address the last filled in cell
in a specific column?

Thanks,
Cat