All right one last question... I need to figure out how to reference the cell
below in another column. I know how to reference the same row in another
column but when I try to reference the cell below that by doing
=MATCH(99^99,A:A)+1, it returns N/A, and then if I put dashes in, it works,
but for some reason it will return the very last value of the other row.
What should I do? Basically currently I have cell T29 and I want to
reference cell R30, but next week it will be T30, and R31.

"Ragdyer" wrote:

You can use this to get the last number in, say Column A:

=LOOKUP(99^99,A:A)

And use this to get the row number of that last number:

=MATCH(99^99,A:A)

SO, to get the *next to last* row number:

=MATCH(99^99,A:A)-1

To get that value in that *next to last* row number:

=INDEX(A:A,MATCH(99^99,A:A)-1)

Finally, to subtract the last number by the next to last number:

=LOOKUP(99^99,A:A)-INDEX(A:A,MATCH(99^99,A:A)-1)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"conny" wrote in message
...
I figured that question out... but is there any way to reference the cell
directly above the last cell, so I can take the most recent's week and
subtract it by the previous week's?

"conny" wrote:

Thanks Gord,
That worked. Now I'm not sure if you can do this, but I need to take
this
last value that I find and subtract it by a value that is in the same
row two
columns over. Is there a way to make sure that the value I am taking is
in
the same row as that last value of the other column?

"Gord Dibben" wrote:

conny

=MATCH(10^10,A:A) to get the last number in a column

=LOOKUP(REPT("z",255),B:B) to get the last non-number in a column

=LOOKUP(2,1/(A1:A65535<""),A1:A65535) to get either number or text


Gord Dibben MS Excel MVP

On Sun, 24 Jun 2007 12:01:02 -0700, conny

wrote:

Hey,
I was wondering if there was any way to create a formula that
references the
last cell with a value in a column. This excel worksheet we have is
updated
with a new value for the 2007 column every week, therefore we are
just copy
and pasting our simple formulas one cell down each week. I was
wondering if
there was any way to set a formula up that would just take the last
cell that
has an entry in it and then start our formulas with that reference
point. If
you need further clarification, just write back. Thanks.