Help referencing last cell in column

[Otter]

I don't know. I just don't know and I've tried looking it up in
Google and HELP and asking our resident EXCEL whiz, and I know it's
gotta be something simple, and here's my problem:

Two sheets in a workbook and the front one is the pretty one with a
photo for background while the second one is where all the data and
formulae are. I can cipher and format and manipulate data til the
cows come home. What I want is for the last (read bottom) cell in
column A of sheet two to show up in a cell I choose on sheet 1. Let's
say that sheet one has "Current worm reproductive rate:" in cell D3
and I want D4 to contain the value of that last cell in column A of
sheet two <pant><pant><Wheeze><wheeze>... somebody please help...
please ?

Muchos gracias (Californian for "merci beaucoup")

Eddie

 

[acw]

Eddie

In D4 of sheet 1, array enter (shift, ctrl, enter) the
following formula.

=INDEX(Sheet2!A:A,MAX(IF(NOT(ISBLANK(Sheet2!A1:A65535)),ROW
(Sheet2!A1:A65535),0)))


Tony

 

[JMay]

Something like this might get you started:

Sub TestMe()
Dim LrowB As Long
LrowB = Range("B65536").End(xlUp).Row
Range("E10") = "B" & LrowB
End Sub

Change accordingly....

HTH

 

[Richard Choate]

For crying out loud, if you want a formula that will always give you the
contents of a cell that is the bottom cell in a list that is growing, use
"=INDEX(A:A,COUNT(A:A))" without the quotes. "A:A" represents the column
where the list is. You do not need an array formula or a macro. Just enter
this is normal and you will see. Adjust to reflect the correct column and
sheet.
Richard Choate

I don't know. I just don't know and I've tried looking it up in
Google and HELP and asking our resident EXCEL whiz, and I know it's
gotta be something simple, and here's my problem:

Two sheets in a workbook and the front one is the pretty one with a
photo for background while the second one is where all the data and
formulae are. I can cipher and format and manipulate data til the
cows come home. What I want is for the last (read bottom) cell in
column A of sheet two to show up in a cell I choose on sheet 1. Let's
say that sheet one has "Current worm reproductive rate:" in cell D3
and I want D4 to contain the value of that last cell in column A of
sheet two <pant><pant><Wheeze><wheeze>... somebody please help...
please ?

Muchos gracias (Californian for "merci beaucoup")

Eddie

 

[Mike]

While Richard's formula is the most simple and will work
in most situations, it will not work if there are blanks
within the column. Acw's array formula takes this into
account and therefore is the superior solution. The
macro, well I just don't know why that was suggested.

 

[Biff]

Hi Mike,

I just tested Richard's formula in a column with 31 rows.
NO blank cells. All containing random single letters with
general formatting. The formula returned the value in row
26 and I can't figure out why.

Biff

 

[Thomas]

Count only counts numbers,if you had 26 numbers and 5 text characters in
the column it would return whatever was in row 26.
INDEX(A:A,COUNTA(A:A)) would include text in a range with no blanks.

 

[Richard Choate]

I respectfully disagree. If you can avoid a resource hogging array formula
with a very simple non-array formula, then you do it. The OP did not
indicate that there will be blanks in the data. I would agree that if blanks
will exist then the array is really the only choice, but if blanks will not
occur in the data, then the simple formula is the only smart way to go. I'm
sure the OP knows which would be the case. I will also add that the OP has
not responded to any of this so it is looking more like a debate between all
of us as to the best solution to a simple problem, while the original poster
no longer even cares.
Richard

While Richard's formula is the most simple and will work
in most situations, it will not work if there are blanks
within the column. Acw's array formula takes this into
account and therefore is the superior solution. The
macro, well I just don't know why that was suggested.

 

[Biff]

Hi Thomas,

I hit the send button before I was ready. I left out that
I would think the formula should have returned 0 because
as you say count() only counts numbers. It not only didn't
find the last value in the column, but it returned a text
value. That's what I can't figure out?

Biff

 

[Thomas]

Think it returns 0 but displays the first character in the index.
d
s
x

returns d for me..

 

[Biff]

Richard,

Yes that's it. Seems to me that the formula should return
0 to indicate no numbers were found or at least #N/A.
By the way, I know that the formula wasn't written to find
text values. I just like to tinker to see what happens.
One other thing on the original question, try this:

=LOOKUP(LOOKUP_VALUE,RANGE)

This will return the last number in a range that is not
greater than LOOKUP_VALUE. The number used as LOOKUP_VALUE
is a matter of debate. For example, I have seen it posted
here as:

=LOOKUP(9.99999999999999E+307,A:A)

Using a number this large ensures that any number found
will not be greater than LOOKUP_VALUE because that is the
largest number that can be entered into a cell. I prefer
using a more realistic number. For example, column A
contains the numbers and you know that the numbers are
always in a range between say, 5000 to 10000. Then:

=LOOKUP(10001,A:A)

Will return the number in the last cell in column A. Also,
the column can contain blanks and formulas that return "".
The column can also contain text but the formula will
return the *last number* found not greater than
LOOKUP_VALUE even if it's not in the *last cell* of the
column.

Biff

 

[Richard Choate]

You've certianly put a lot of thought into that. These days I guess I'm just
too busy to tinker.
See you on the next one.
Richard

Richard,

Yes that's it. Seems to me that the formula should return
0 to indicate no numbers were found or at least #N/A.
By the way, I know that the formula wasn't written to find
text values. I just like to tinker to see what happens.
One other thing on the original question, try this:

=LOOKUP(LOOKUP_VALUE,RANGE)

This will return the last number in a range that is not
greater than LOOKUP_VALUE. The number used as LOOKUP_VALUE
is a matter of debate. For example, I have seen it posted
here as:

=LOOKUP(9.99999999999999E+307,A:A)

Using a number this large ensures that any number found
will not be greater than LOOKUP_VALUE because that is the
largest number that can be entered into a cell. I prefer
using a more realistic number. For example, column A
contains the numbers and you know that the numbers are
always in a range between say, 5000 to 10000. Then:

=LOOKUP(10001,A:A)

Will return the number in the last cell in column A. Also,
the column can contain blanks and formulas that return "".
The column can also contain text but the formula will
return the *last number* found not greater than
LOOKUP_VALUE even if it's not in the *last cell* of the
column.

Biff

 

[Otter]

I just wanted to thank everyone for their answers regarding the
problem of finding the last cell in a column, and as good as they all
are (and an incentive to explore some other neat features of EXCEL),
Richard Choate wins the Otter Olive Laurel for Simplest Solution. It
works like a charm and does exactly what I need with a minimum of
fuss. Thank you everyone for your help.

Eddie

 

[Richard Choate]

You're welcome
Richard Choate


I just wanted to thank everyone for their answers regarding the
problem of finding the last cell in a column, and as good as they all
are (and an incentive to explore some other neat features of EXCEL),
Richard Choate wins the Otter Olive Laurel for Simplest Solution. It
works like a charm and does exactly what I need with a minimum of
fuss. Thank you everyone for your help.

Eddie