I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000<"")))))

into the spot where you want to retrieve the contents of the last used cell
in row B. Set the range to cover your maximum expected row in the column
(you can't use B:B for the entire column as SUMPRODUCT does not allow for
that generalization).

Rick

This will return the last numeric value in column A:

=LOOKUP(1E10,A:A)

--
Biff
Microsoft Excel MVP


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

Formula or macro?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

Will return the last value entered in column A.
Press CTRL+SHIFT+ENTER not only ENTER.


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

No need to array enter it


--


Regards,


Peo Sjoblom


"Gary" wrote in message
...
=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

Will return the last value entered in column A.
Press CTRL+SHIFT+ENTER not only ENTER.


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000<"")))))

into the spot where you want to retrieve the contents of the last used cell
in row B. Set the range to cover your maximum expected row in the column
(you can't use B:B for the entire column as SUMPRODUCT does not allow for
that generalization).

Rick

This formula works very nicely! I also realize the "1E10" may be substituted
with however many rows are to be searched; very interesting. Thank you so
much for teaching me something new.

"T. Valko" wrote:

This will return the last numeric value in column A:

=LOOKUP(1E10,A:A)

--
Biff
Microsoft Excel MVP


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

Hi Don, this was for a formula. I've had some great results with other
feedback, but really appreciate your willingness to help out as well! Thanks
so much!

"Don Guillett" wrote:

Formula or macro?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

I think you should use the formula that Gary posted instead of my formula...
it should be more efficient. And note Peo's comment that Gary's formula can
be committed by just using the Enter Key instead of the key combo Gary
mentioned.

Rick


"BrendaN_at_Welke_Customs"
wrote in message ...
IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000<"")))))

into the spot where you want to retrieve the contents of the last used
cell
in row B. Set the range to cover your maximum expected row in the column
(you can't use B:B for the entire column as SUMPRODUCT does not allow for
that generalization).

Rick

Thanks Gary, this works very well! But I'm not sure about the keystrokes
you've indicated... they did not appear to be necessary (and nothing
different happened when I tried the combination), and it worked fine with
just ENTER.

"Gary" wrote:

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

Will return the last value entered in column A.
Press CTRL+SHIFT+ENTER not only ENTER.


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into a
formula elsewhere. A new number is added daily to this column. There are
empty cells throughout the column. Thanks!

I'd use the formula Biff suggested! <g

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think you should use the formula that Gary posted instead of my
formula... it should be more efficient. And note Peo's comment that Gary's
formula can be committed by just using the Enter Key instead of the key
combo Gary mentioned.

Rick


"BrendaN_at_Welke_Customs"
wrote in message
...
IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000<"")))))

into the spot where you want to retrieve the contents of the last used
cell
in row B. Set the range to cover your maximum expected row in the column
(you can't use B:B for the entire column as SUMPRODUCT does not allow
for
that generalization).

Rick

No, the 1E10 is the lookup_value.

1E10 = 10,000,000,000 (10 billion)

The way that LOOKUP works is if the lookup_value is greater than any value
in the range then it returns the *last* value that is less than the
lookup_value. To insure that we get the *last* number in the range we use an
arbitrary huge number like 10 billion. If you we working with numbers that
big then we just use an even bigger number for the lookup_value like 1E100.
I don't even know what that value is called! <g

Consider this example. Suppose you were working with bowling scores. The
highest possible bowling score is 300. In this case we know for certain what
kind of numbers we're dealing with so we don't need a huge arbitrary
lookup_value like 1E10. We just need a number that is guaranteed to greater
than 300. So, we can use 301.

=LOOKUP(301,A:A)

--
Biff
Microsoft Excel MVP


"BrendaN_at_Welke_Customs"
wrote in message ...
This formula works very nicely! I also realize the "1E10" may be
substituted
with however many rows are to be searched; very interesting. Thank you
so
much for teaching me something new.

"T. Valko" wrote:

This will return the last numeric value in column A:

=LOOKUP(1E10,A:A)

--
Biff
Microsoft Excel MVP


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

But that only finds the last number in a column even if there is text after
it (and throws an error if there isn't a number in the column)... Gary's
formula finds the last used cell in the column whether that cell has a
number or text in it (which is what my formula did also, but more
inefficiently, I would think)

Rick


"T. Valko" wrote in message
...
I'd use the formula Biff suggested! <g

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think you should use the formula that Gary posted instead of my
formula... it should be more efficient. And note Peo's comment that Gary's
formula can be committed by just using the Enter Key instead of the key
combo Gary mentioned.

Rick


"BrendaN_at_Welke_Customs"
wrote in message
...
IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value
into a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000<"")))))

into the spot where you want to retrieve the contents of the last used
cell
in row B. Set the range to cover your maximum expected row in the
column
(you can't use B:B for the entire column as SUMPRODUCT does not allow
for
that generalization).

Rick

That's what the OP wants, the last NUMBER in the range.

which is what my formula did also, but more inefficiently, I would think

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

I'll do some tests later on tonight.


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
But that only finds the last number in a column even if there is text
after it (and throws an error if there isn't a number in the column)...
Gary's formula finds the last used cell in the column whether that cell
has a number or text in it (which is what my formula did also, but more
inefficiently, I would think)

Rick


"T. Valko" wrote in message
...
I'd use the formula Biff suggested! <g

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think you should use the formula that Gary posted instead of my
formula... it should be more efficient. And note Peo's comment that
Gary's formula can be committed by just using the Enter Key instead of
the key combo Gary mentioned.

Rick


"BrendaN_at_Welke_Customs"
wrote in message
...
IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value
into a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000<"")))))

into the spot where you want to retrieve the contents of the last used
cell
in row B. Set the range to cover your maximum expected row in the
column
(you can't use B:B for the entire column as SUMPRODUCT does not allow
for
that generalization).

Rick

...use an even bigger number for the lookup_value like 1E100.
I don't even know what that value is called! <g

It's called a "google"....

http://www.unc.edu/~rowlett/units/dictG.html

Rick

But that only finds the last number in a column even if there is text
after it (and throws an error if there isn't a number in the column)...
Gary's formula finds the last used cell in the column whether that cell
has a number or text in it (which is what my formula did also, but more
inefficiently, I would think)

That's what the OP wants, the last NUMBER in the range.

Hey, you are right! I locked on OP phrase "last entry in a column" and for
some didn't let "a new number is added daily" register with me. Okay, then
yes, I would use your formula instead of Gary's... for this particular
question<g.


which is what my formula did also, but more inefficiently, I would think

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

I'll do some tests later on tonight.

Thanks... I'd be interested in the results.


Rick

I stand corrected... I missed that you had said "a new number is added
daily"... use Biff's (T.Valko's) formula instead of Gary's for your problem.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think you should use the formula that Gary posted instead of my
formula... it should be more efficient. And note Peo's comment that Gary's
formula can be committed by just using the Enter Key instead of the key
combo Gary mentioned.

Rick


"BrendaN_at_Welke_Customs"
wrote in message
...
IT WORKS! AWESOME! Thank you so much.

"Rick Rothstein (MVP - VB)" wrote:

I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Assuming your column is B, you should be able to insert this....

INDEX(B1:B1000,SUMPRODUCT(MAX((ROW(B1:B1000)*(B1:B 1000<"")))))

into the spot where you want to retrieve the contents of the last used
cell
in row B. Set the range to cover your maximum expected row in the column
(you can't use B:B for the entire column as SUMPRODUCT does not allow
for
that generalization).

Rick

You are right. It works fine even if you just press enter.
Sorry for the confusion.

"BrendaN_at_Welke_Customs"
wrote in message ...
Thanks Gary, this works very well! But I'm not sure about the keystrokes
you've indicated... they did not appear to be necessary (and nothing
different happened when I tried the combination), and it worked fine with
just ENTER.

"Gary" wrote:

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

Will return the last value entered in column A.
Press CTRL+SHIFT+ENTER not only ENTER.


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

Calculation times:

http://img229.imageshack.us/img229/8...actimesje1.jpg

Not much difference.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
But that only finds the last number in a column even if there is text
after it (and throws an error if there isn't a number in the column)...
Gary's formula finds the last used cell in the column whether that cell
has a number or text in it (which is what my formula did also, but more
inefficiently, I would think)

That's what the OP wants, the last NUMBER in the range.

Hey, you are right! I locked on OP phrase "last entry in a column" and for
some didn't let "a new number is added daily" register with me. Okay, then
yes, I would use your formula instead of Gary's... for this particular
question<g.


which is what my formula did also, but more inefficiently, I would think

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

I'll do some tests later on tonight.

Thanks... I'd be interested in the results.


Rick

I find that very surprising. My formula uses 4 function calls and Gary's
uses 1... one would think mine would have been much slower. Kind of makes me
think LOOKUP is not a very efficient function underneath it all. Thanks for
running the test; I really appreciate it.

Rick


"T. Valko" wrote in message
...
Calculation times:

http://img229.imageshack.us/img229/8...actimesje1.jpg

Not much difference.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
But that only finds the last number in a column even if there is text
after it (and throws an error if there isn't a number in the column)...
Gary's formula finds the last used cell in the column whether that cell
has a number or text in it (which is what my formula did also, but more
inefficiently, I would think)

That's what the OP wants, the last NUMBER in the range.

Hey, you are right! I locked on OP phrase "last entry in a column" and
for some didn't let "a new number is added daily" register with me. Okay,
then yes, I would use your formula instead of Gary's... for this
particular question<g.


which is what my formula did also, but more inefficiently, I would think

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

I'll do some tests later on tonight.

Thanks... I'd be interested in the results.


Rick

I find that very surprising.

I was surprised too. I thought SUMPRODUCT would be faster.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I find that very surprising. My formula uses 4 function calls and Gary's
uses 1... one would think mine would have been much slower. Kind of makes
me think LOOKUP is not a very efficient function underneath it all. Thanks
for running the test; I really appreciate it.

Rick


"T. Valko" wrote in message
...
Calculation times:

http://img229.imageshack.us/img229/8...actimesje1.jpg

Not much difference.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
But that only finds the last number in a column even if there is text
after it (and throws an error if there isn't a number in the
column)... Gary's formula finds the last used cell in the column
whether that cell has a number or text in it (which is what my formula
did also, but more inefficiently, I would think)

That's what the OP wants, the last NUMBER in the range.

Hey, you are right! I locked on OP phrase "last entry in a column" and
for some didn't let "a new number is added daily" register with me.
Okay, then yes, I would use your formula instead of Gary's... for this
particular question<g.


which is what my formula did also, but more inefficiently, I would
think

=LOOKUP(2,1/(A1:A65535<""),A1:A65535)

I'll do some tests later on tonight.

Thanks... I'd be interested in the results.


Rick

"T. Valko" wrote...
I find that very surprising.

I was surprised too. I thought SUMPRODUCT would be faster.
....

The SUMPRODUCT on its own is almost certainly faster, but all the
stuff in the MAX call takes time to process.

Another academic question would be how an array formula like

=INDEX(rngA,MAX(IF(rngB<"",ROW(rngB))))

would compare.

ANY number larger than any number in the column would do.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BrendaN_at_Welke_Customs"
wrote in message ...
This formula works very nicely! I also realize the "1E10" may be
substituted
with however many rows are to be searched; very interesting. Thank you
so
much for teaching me something new.

"T. Valko" wrote:

This will return the last numeric value in column A:

=LOOKUP(1E10,A:A)

--
Biff
Microsoft Excel MVP


"BrendaN_at_Welke_Customs"
m wrote in message
...
I want to select the last entry in a column, and insert this value into
a
formula elsewhere. A new number is added daily to this column. There
are
empty cells throughout the column. Thanks!

"Harlan Grove" wrote in message
oups.com...
"T. Valko" wrote...
I find that very surprising.

I was surprised too. I thought SUMPRODUCT would be faster.
...

The SUMPRODUCT on its own is almost certainly faster, but all the
stuff in the MAX call takes time to process.

Another academic question would be how an array formula like

=INDEX(rngA,MAX(IF(rngB<"",ROW(rngB))))

would compare.


It compares favorably when the start of the range is row 1:

http://img502.imageshack.us/img502/5...times11ng0.jpg

But it's slower, as is SUMPRODUCT, when you need to calculate the row
offset.

--
Biff
Microsoft Excel MVP