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

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

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

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

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

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

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!

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!

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!

...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

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!

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!

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!

=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!

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!

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!