I have numerous sheets within a book where all cells in column C in all
sheets have the following formula €œ=IF(ISBLANK(P4),"",(R3-P4))€�. For you
reference both columns P and R hold a monetary value and are formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.

Any help offered would be appreciated.

If the cells are always filled up to the last cell meaning if the first
filled cell is in C2 and the last cell in C30 all the cells in-between are
filled you can simply use

=INDEX(C2:C10000,COUNT(C2:C10000))

change the ranges accordingly if you think you will fill more than 10000
cells

post back if it's not that way

Regards,

Peo Sjoblom

"Pank" wrote in message
...
I have numerous sheets within a book where all cells in column C in all
sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". For you
reference both columns P and R hold a monetary value and are formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the
contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.

Any help offered would be appreciated.

If you want the value of the last numeric value in the column range....

Try something like this:
D1: =LOOKUP(10^99,C1:C20)

Adjust the range reference to suit your situation.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

I have numerous sheets within a book where all cells in column C in all
sheets have the following formula €œ=IF(ISBLANK(P4),"",(R3-P4))€�. For you
reference both columns P and R hold a monetary value and are formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.

Any help offered would be appreciated.

Peo,

Thanks just what I wanted.

Thank U.

"Peo Sjoblom" wrote:

If the cells are always filled up to the last cell meaning if the first
filled cell is in C2 and the last cell in C30 all the cells in-between are
filled you can simply use

=INDEX(C2:C10000,COUNT(C2:C10000))

change the ranges accordingly if you think you will fill more than 10000
cells

post back if it's not that way

Regards,

Peo Sjoblom

"Pank" wrote in message
...
I have numerous sheets within a book where all cells in column C in all
sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". For you
reference both columns P and R hold a monetary value and are formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the
contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.

Any help offered would be appreciated.

Ron,

Thar worked a treat as well.

I understand the lookup, however, I do not understand the Can you please
explain what the 10^99, can you please explain.

If possible, can you also explain Peo solution.

Thank you for your time.

"Ron Coderre" wrote:

If you want the value of the last numeric value in the column range....

Try something like this:
D1: =LOOKUP(10^99,C1:C20)

Adjust the range reference to suit your situation.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

I have numerous sheets within a book where all cells in column C in all
sheets have the following formula €œ=IF(ISBLANK(P4),"",(R3-P4))€�. For you
reference both columns P and R hold a monetary value and are formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.

Any help offered would be appreciated.

Through a fortunate quirk in the LOOKUP function, if you use it to find a
value that is greater than any other value in the list...it returns the last
item of that type (text or numeric) in the list. Excel's maximum possible
number is 9.99999999999999E307, but I use 10^99 to avoid all that typing.

If you were looking for the last text value in a column range, this would
work:
=LOOKUP(REPT("z",255),C2:C20)
Note: REPT("z",255) returns a string of 255 z's

Regarding Peo's solution, the COUNT function returns the count of numeric
cells. As long as the series of numeric values is contiguous (no blanks or
text within the list), it returns the position of the last numeric cell
within the list.

In Peo's application, the INDEX function returns the n-th item in C2:C10000,
where "n" is the count of numeric cells.

I hope that helps.
(Post back if you have more questions)
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

Ron,

Thar worked a treat as well.

I understand the lookup, however, I do not understand the Can you please
explain what the 10^99, can you please explain.

If possible, can you also explain Peo solution.

Thank you for your time.

"Ron Coderre" wrote:

If you want the value of the last numeric value in the column range....

Try something like this:
D1: =LOOKUP(10^99,C1:C20)

Adjust the range reference to suit your situation.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

I have numerous sheets within a book where all cells in column C in all
sheets have the following formula €œ=IF(ISBLANK(P4),"",(R3-P4))€�. For you
reference both columns P and R hold a monetary value and are formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.

Any help offered would be appreciated.

Ron,

Thank U for explaining. So much to learn and put into pratice.

Regards

"Ron Coderre" wrote:

Through a fortunate quirk in the LOOKUP function, if you use it to find a
value that is greater than any other value in the list...it returns the last
item of that type (text or numeric) in the list. Excel's maximum possible
number is 9.99999999999999E307, but I use 10^99 to avoid all that typing.

If you were looking for the last text value in a column range, this would
work:
=LOOKUP(REPT("z",255),C2:C20)
Note: REPT("z",255) returns a string of 255 z's

Regarding Peo's solution, the COUNT function returns the count of numeric
cells. As long as the series of numeric values is contiguous (no blanks or
text within the list), it returns the position of the last numeric cell
within the list.

In Peo's application, the INDEX function returns the n-th item in C2:C10000,
where "n" is the count of numeric cells.

I hope that helps.
(Post back if you have more questions)
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

Ron,

Thar worked a treat as well.

I understand the lookup, however, I do not understand the Can you please
explain what the 10^99, can you please explain.

If possible, can you also explain Peo solution.

Thank you for your time.

"Ron Coderre" wrote:

If you want the value of the last numeric value in the column range....

Try something like this:
D1: =LOOKUP(10^99,C1:C20)

Adjust the range reference to suit your situation.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

I have numerous sheets within a book where all cells in column C in all
sheets have the following formula €œ=IF(ISBLANK(P4),"",(R3-P4))€�. For you
reference both columns P and R hold a monetary value and are formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900.

Any help offered would be appreciated.

"<<<Through a fortunate quirk in the LOOKUP function"

I wouldn't exactly describe it as a "quirk".<g

I believe it's more like a computer program "blindly" following the code of
the author.

Since Lookup() is *supposed* to be properly used on *only* an ascending
ordered list,
*AND*
If Lookup() can't find the lookup value, it uses the largest value in the
array (list) that is less than or equal to lookup value,
Lookup() assumes the *largest* value is the *LAST* value, since it is
programmed to believe the list is sorted, ascending.
And we make the lookup value larger then any number that would *normally*
exist in the list:
10^99 - 99^99

Or we make it as large as any number which *can* exist in the list:
9.99999999999999E307

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" wrote in message
...
Through a fortunate quirk in the LOOKUP function, if you use it to find a
value that is greater than any other value in the list...it returns the
last
item of that type (text or numeric) in the list. Excel's maximum possible
number is 9.99999999999999E307, but I use 10^99 to avoid all that typing.

If you were looking for the last text value in a column range, this would
work:
=LOOKUP(REPT("z",255),C2:C20)
Note: REPT("z",255) returns a string of 255 z's

Regarding Peo's solution, the COUNT function returns the count of numeric
cells. As long as the series of numeric values is contiguous (no blanks
or
text within the list), it returns the position of the last numeric cell
within the list.

In Peo's application, the INDEX function returns the n-th item in
C2:C10000,
where "n" is the count of numeric cells.

I hope that helps.
(Post back if you have more questions)
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

Ron,

Thar worked a treat as well.

I understand the lookup, however, I do not understand the Can you please
explain what the 10^99, can you please explain.

If possible, can you also explain Peo solution.

Thank you for your time.

"Ron Coderre" wrote:

If you want the value of the last numeric value in the column range....

Try something like this:
D1: =LOOKUP(10^99,C1:C20)

Adjust the range reference to suit your situation.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

I have numerous sheets within a book where all cells in column C in
all
sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". For
you
reference both columns P and R hold a monetary value and are
formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the
contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be
200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be
250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be
900.

Any help offered would be appreciated.

You didn't like "quirk", RD?

It seems like we both described the same function behavior.
However, I apologize to LOOKUP() if I disparaged it in any way. <vbg

Best Regards,

Ron


"Ragdyer" wrote in message
...
"<<<Through a fortunate quirk in the LOOKUP function"

I wouldn't exactly describe it as a "quirk".<g

I believe it's more like a computer program "blindly" following the code
of the author.

Since Lookup() is *supposed* to be properly used on *only* an ascending
ordered list,
*AND*
If Lookup() can't find the lookup value, it uses the largest value in the
array (list) that is less than or equal to lookup value,
Lookup() assumes the *largest* value is the *LAST* value, since it is
programmed to believe the list is sorted, ascending.
And we make the lookup value larger then any number that would *normally*
exist in the list:
10^99 - 99^99

Or we make it as large as any number which *can* exist in the list:
9.99999999999999E307

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" wrote in message
...
Through a fortunate quirk in the LOOKUP function, if you use it to find
a
value that is greater than any other value in the list...it returns the
last
item of that type (text or numeric) in the list. Excel's maximum
possible
number is 9.99999999999999E307, but I use 10^99 to avoid all that typing.

If you were looking for the last text value in a column range, this would
work:
=LOOKUP(REPT("z",255),C2:C20)
Note: REPT("z",255) returns a string of 255 z's

Regarding Peo's solution, the COUNT function returns the count of
numeric
cells. As long as the series of numeric values is contiguous (no blanks
or
text within the list), it returns the position of the last numeric cell
within the list.

In Peo's application, the INDEX function returns the n-th item in
C2:C10000,
where "n" is the count of numeric cells.

I hope that helps.
(Post back if you have more questions)
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

Ron,

Thar worked a treat as well.

I understand the lookup, however, I do not understand the Can you please
explain what the 10^99, can you please explain.

If possible, can you also explain Peo solution.

Thank you for your time.

"Ron Coderre" wrote:

If you want the value of the last numeric value in the column
range....

Try something like this:
D1: =LOOKUP(10^99,C1:C20)

Adjust the range reference to suit your situation.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

I have numerous sheets within a book where all cells in column C in
all
sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))". For
you
reference both columns P and R hold a monetary value and are
formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the
contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be
200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be
250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be
900.

Any help offered would be appreciated.

Come to think of it, you're label is probably appropo.<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" wrote in message
...
You didn't like "quirk", RD?

It seems like we both described the same function behavior.
However, I apologize to LOOKUP() if I disparaged it in any way. <vbg

Best Regards,

Ron


"Ragdyer" wrote in message
...
"<<<Through a fortunate quirk in the LOOKUP function"

I wouldn't exactly describe it as a "quirk".<g

I believe it's more like a computer program "blindly" following the code
of the author.

Since Lookup() is *supposed* to be properly used on *only* an ascending
ordered list,
*AND*
If Lookup() can't find the lookup value, it uses the largest value in the
array (list) that is less than or equal to lookup value,
Lookup() assumes the *largest* value is the *LAST* value, since it is
programmed to believe the list is sorted, ascending.
And we make the lookup value larger then any number that would *normally*
exist in the list:
10^99 - 99^99

Or we make it as large as any number which *can* exist in the list:
9.99999999999999E307

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ron Coderre" wrote in message
...
Through a fortunate quirk in the LOOKUP function, if you use it to find
a
value that is greater than any other value in the list...it returns the
last
item of that type (text or numeric) in the list. Excel's maximum
possible
number is 9.99999999999999E307, but I use 10^99 to avoid all that
typing.

If you were looking for the last text value in a column range, this
would
work:
=LOOKUP(REPT("z",255),C2:C20)
Note: REPT("z",255) returns a string of 255 z's

Regarding Peo's solution, the COUNT function returns the count of
numeric
cells. As long as the series of numeric values is contiguous (no blanks
or
text within the list), it returns the position of the last numeric cell
within the list.

In Peo's application, the INDEX function returns the n-th item in
C2:C10000,
where "n" is the count of numeric cells.

I hope that helps.
(Post back if you have more questions)
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

Ron,

Thar worked a treat as well.

I understand the lookup, however, I do not understand the Can you
please
explain what the 10^99, can you please explain.

If possible, can you also explain Peo solution.

Thank you for your time.

"Ron Coderre" wrote:

If you want the value of the last numeric value in the column
range....

Try something like this:
D1: =LOOKUP(10^99,C1:C20)

Adjust the range reference to suit your situation.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Pank" wrote:

I have numerous sheets within a book where all cells in column C in
all
sheets have the following formula "=IF(ISBLANK(P4),"",(R3-P4))".
For you
reference both columns P and R hold a monetary value and are
formatted as
Currency.

Is there a way that cell D1 can automatically be populated with the
contents
of the last cell in column C that has a value in it.

E.G.

Sheet 1, cell C19 has a value of 200, therefore cell D1 should be
200.
Sheet 2, cell C25 has a value of 250, therefore cell D1 should be
250.
Sheet 3, cell C99 has a value of 900, therefore cell D1 should be
900.

Any help offered would be appreciated.