I need to write a formula in one cell that inserts the last used value from a
range of cells. My range of cells represents monthly values. So, for
example, if I have only posted values through July then only the first seven
cells will have values and the value I want to insert is the July or 7th
value.

Enter this UDF:

Function last_used_value(R As Range) As Variant
Dim rr As Range
last_used_value = 0
For Each rr In R
If rr.Value = "" Then
Else
last_used_value = rr.Value
End If
Next
End Function

This function will report the last filled value in a range that is not
blank. The range can be any block of cells, rows, columns, etc. Use it like:

=last_used_value(E2:H7) to get the last used value in the block.
--
Gary's Student


"samfw" wrote:

I need to write a formula in one cell that inserts the last used value from a
range of cells. My range of cells represents monthly values. So, for
example, if I have only posted values through July then only the first seven
cells will have values and the value I want to insert is the July or 7th
value.

Wouldn't

=LOOKUP(9.99999999999999E+307,Range)

do the job?

samfw wrote:
I need to write a formula in one cell that inserts the last used value from a
range of cells. My range of cells represents monthly values. So, for
example, if I have only posted values through July then only the first seven
cells will have values and the value I want to insert is the July or 7th
value.

This worked, (much simpler than the other suggestion) but I wonder why? What
is the purpose of +307? In my case, 99.99999999 +nothing worked just fine.
I'm just wondering how and why the formula works?

Thanks,
McH


"Aladin Akyurek" wrote:

Wouldn't

=LOOKUP(9.99999999999999E+307,Range)

do the job?

samfw wrote:
I need to write a formula in one cell that inserts the last used value from a
range of cells. My range of cells represents monthly values. So, for
example, if I have only posted values through July then only the first seven
cells will have values and the value I want to insert is the July or 7th
value.

It's the biggest number you can use in excel and the formula is built on a
bug in Excel
For instance if the second but last value would be larger than the lookup
value it would return that value instead
This is just to make sure it doesn't happen OTOH if you values are always
entered from top to bottom and there are no blanks in-between you can also
use

=INDEX(A:A,COUNTA(A:A))

the risk of using a bug in a formula is of course if MS ever would to
correct this bug it won't work in a future version. However that is not very
likely

--
Regards,

Peo Sjoblom

Portland, Oregon




"Bob McHenry" <Bob wrote in message
...
This worked, (much simpler than the other suggestion) but I wonder why?
What
is the purpose of +307? In my case, 99.99999999 +nothing worked just
fine.
I'm just wondering how and why the formula works?

Thanks,
McH


"Aladin Akyurek" wrote:

Wouldn't

=LOOKUP(9.99999999999999E+307,Range)

do the job?

samfw wrote:
I need to write a formula in one cell that inserts the last used value
from a
range of cells. My range of cells represents monthly values. So, for
example, if I have only posted values through July then only the first
seven
cells will have values and the value I want to insert is the July or
7th
value.