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.

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.

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

will return the last numeric value in column A. For a really good
discussion of the topic, see:

http://www.xldynamic.com/source/xld....l#last_numeric
--
Gary''s Student - gsnu200733


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

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.

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.

Sun, 24 Jun 2007 12:01:02 -0700 from conny
:
I was wondering if there was any way to create a formula

Please don't post the same query multiple times.


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

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.

Thanks... I guess I'm an idiot! I appreciate the help!

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

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.

never mind, I was using a look up and then i changed to an index function and
it worked... Thanks for all the help!

"conny" wrote:

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.

You're welcome, and glad to see you were able to work things out.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"conny" wrote in message
...
never mind, I was using a look up and then i changed to an index function
and
it worked... Thanks for all the help!

"conny" wrote:

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.