Find Last data in a column
 

I am trying to get Excel to sum the last entry in a column in one worksheet
with the last in another worksheet. The last entries may not be either Max or
Min values, but the last entered, by the way, these values are the result of
a calculation in the sheets.
Can anyone help?
--
Steve H

Find Last data in a column
 

If there are NO blank entries in the columns then

=INDEX(A:A,COUNTA(A:A)) will give las entry in column A

If there are blanks then:

=LOOKUP(99^99,A:A)

this will sum last entries in columns A & D

=SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D))

or simply

=LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)

HTH


"Steve" wrote:

I am trying to get Excel to sum the last entry in a column in one worksheet
with the last in another worksheet. The last entries may not be either Max or
Min values, but the last entered, by the way, these values are the result of
a calculation in the sheets.
Can anyone help?
--
Steve H

Find Last data in a column
 

=LOOKUP(2,1/(D1:D65535<""),D:D)+LOOKUP(2,1/(Sheet2!E1:E65535<""),Sheet2!E:E)

In XL2007

=LOOKUP(2,1/(D:D<""),D:D)+LOOKUP(2,1/(Sheet2!E:E<""),Sheet2!E:E)



"Steve" wrote:

I am trying to get Excel to sum the last entry in a column in one worksheet
with the last in another worksheet. The last entries may not be either Max or
Min values, but the last entered, by the way, these values are the result of
a calculation in the sheets.
Can anyone help?
--
Steve H

Find Last data in a column
 

Teethless mama wrote...
=LOOKUP(2,1/(D1:D65535<""),D:D)
+LOOKUP(2,1/(Sheet2!E1:E65535<""),Sheet2!E:E)
....

What happens when the last entry in a column is nonnumeric text or an
error value? Since the OP wants to sum the results, reasonably safe to
assume OP wants to find the last number in the column, in which case
shorter, more efficient and more robust to use

=LOOKUP(1E+307,D:D)+LOOKUP(1E+307,SheetX!E:E)

Find Last data in a column
 

Thanks Toppers, the LOOKUP function works very well, but the INDEX function
gave quite varying results, sometimes the 3rd last or the 5th last in an date
column (A), and nothing in columns with numbers , currency (F) or percentages.

I came up with my own, slightly more complicated function,
LOOKUP(MAXA($A$4:$A$53),$A$4:$A$53,F4:F53), which not only finds the last
date, but uses that to return the value on that date.

But anyway,
Thanks for your reply,
--
Steve H


"Toppers" wrote:

If there are NO blank entries in the columns then

=INDEX(A:A,COUNTA(A:A)) will give las entry in column A

If there are blanks then:

=LOOKUP(99^99,A:A)

this will sum last entries in columns A & D

=SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D))

or simply

=LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)

HTH


"Steve" wrote:

I am trying to get Excel to sum the last entry in a column in one worksheet
with the last in another worksheet. The last entries may not be either Max or
Min values, but the last entered, by the way, these values are the result of
a calculation in the sheets.
Can anyone help?
--
Steve H

Find Last data in a column
 

But you didn't say anything about dates or other columns in your original post?

"Steve" wrote:

Thanks Toppers, the LOOKUP function works very well, but the INDEX function
gave quite varying results, sometimes the 3rd last or the 5th last in an date
column (A), and nothing in columns with numbers , currency (F) or percentages.

I came up with my own, slightly more complicated function,
LOOKUP(MAXA($A$4:$A$53),$A$4:$A$53,F4:F53), which not only finds the last
date, but uses that to return the value on that date.

But anyway,
Thanks for your reply,
--
Steve H


"Toppers" wrote:

If there are NO blank entries in the columns then

=INDEX(A:A,COUNTA(A:A)) will give las entry in column A

If there are blanks then:

=LOOKUP(99^99,A:A)

this will sum last entries in columns A & D

=SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D))

or simply

=LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)

HTH


"Steve" wrote:

I am trying to get Excel to sum the last entry in a column in one worksheet
with the last in another worksheet. The last entries may not be either Max or
Min values, but the last entered, by the way, these values are the result of
a calculation in the sheets.
Can anyone help?
--
Steve H

Find Last data in a column
 

Yes, I am sorry, but I did'nt see that that would be a problem. I am a
"virgin" in dealing with people that can figure out a problem like this
without turning a hair.
I was unable to find a function that I could see being able to do the job,
so I thought I'd ask the experts. I just wanted to find a "simple or elegant"
way to find the row of last data in a column, and use the row reference to
point to other data.
Next time I'll be precise, I promise!

--
Steve H


"JLatham" wrote:

But you didn't say anything about dates or other columns in your original post?

"Steve" wrote:

Thanks Toppers, the LOOKUP function works very well, but the INDEX function
gave quite varying results, sometimes the 3rd last or the 5th last in an date
column (A), and nothing in columns with numbers , currency (F) or percentages.

I came up with my own, slightly more complicated function,
LOOKUP(MAXA($A$4:$A$53),$A$4:$A$53,F4:F53), which not only finds the last
date, but uses that to return the value on that date.

But anyway,
Thanks for your reply,
--
Steve H


"Toppers" wrote:

If there are NO blank entries in the columns then

=INDEX(A:A,COUNTA(A:A)) will give las entry in column A

If there are blanks then:

=LOOKUP(99^99,A:A)

this will sum last entries in columns A & D

=SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D))

or simply

=LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)

HTH


"Steve" wrote:

I am trying to get Excel to sum the last entry in a column in one worksheet
with the last in another worksheet. The last entries may not be either Max or
Min values, but the last entered, by the way, these values are the result of
a calculation in the sheets.
Can anyone help?
--
Steve H

Find Last data in a column
 

Quite often the type of information being dealt with has an impact on finding
a good solution. Numbers, words and dates are all different animals and have
to be dealt with accordingly. I'm sure Toppers will be pleased to know that
his suggestion helped you come up with a 'spot-on' solution in the end. And
that is what it's all about.
Sometimes it's better to provide a little too much information - the person
reading your request for help can just ignore that part. But not giving
enough can not only be a bit frustrating to all concerned, but can cause
'downstream' problems. Example: someone asked how to move certain rows from
one sheet onto another. I provided code to do that. Then they asked how to
change all sheet references in formulas to a different sheet reference. I
did that. THEN they complained that suddenly their workbook is acting all
screwy and bogging down. The two requests were simple enough when looked at
separately, but I've come to realize that the two of them together have
probably almost hopelessly left Excel with a bunch of formulas that either
don't reference the proper information or don't even reference cells with
values in them at all.

But I really wasn't getting on your case so much as just trying to defend
Toppers a little - did a good job with the info provided. Don't feel bad,
you're not the first to get into this situation, and I am absolutely certain
that you won't be the last <g. On the plus side, what you did ask for, you
did so clearly and succinctly - sometimes that is a rare animal to find in
this zoo also.

"Steve" wrote:

Yes, I am sorry, but I did'nt see that that would be a problem. I am a
"virgin" in dealing with people that can figure out a problem like this
without turning a hair.
I was unable to find a function that I could see being able to do the job,
so I thought I'd ask the experts. I just wanted to find a "simple or elegant"
way to find the row of last data in a column, and use the row reference to
point to other data.
Next time I'll be precise, I promise!

--
Steve H


"JLatham" wrote:

But you didn't say anything about dates or other columns in your original post?

"Steve" wrote:

Thanks Toppers, the LOOKUP function works very well, but the INDEX function
gave quite varying results, sometimes the 3rd last or the 5th last in an date
column (A), and nothing in columns with numbers , currency (F) or percentages.

I came up with my own, slightly more complicated function,
LOOKUP(MAXA($A$4:$A$53),$A$4:$A$53,F4:F53), which not only finds the last
date, but uses that to return the value on that date.

But anyway,
Thanks for your reply,
--
Steve H


"Toppers" wrote:

If there are NO blank entries in the columns then

=INDEX(A:A,COUNTA(A:A)) will give las entry in column A

If there are blanks then:

=LOOKUP(99^99,A:A)

this will sum last entries in columns A & D

=SUM(LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D))

or simply

=LOOKUP(99^99,A:A)+LOOKUP(99^99,D:D)

HTH


"Steve" wrote:

I am trying to get Excel to sum the last entry in a column in one worksheet
with the last in another worksheet. The last entries may not be either Max or
Min values, but the last entered, by the way, these values are the result of
a calculation in the sheets.
Can anyone help?
--
Steve H