Help with displaying the contents of the last populate cell.

[Guest]

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 Sjoblom]

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

 

[Guest]

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

 

[Guest]

Peo,

Thanks just what I wanted.

Thank U.

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Ron,

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

Regards

 

[Ragdyer]

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

 

[Ron Coderre]

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]

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