Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last valid value in array
Hi,
I have written a UDF to search through a range to find the last vali value eg: a b c d e 1 1 5 5 2 6 3 1 7 8 In these examples 5, 6 and 8 would be the last valid enties Basically I transfer the range to an array and search from the end bac until I find a value. The problem is that the values don't update whe data is changed. I had thought about using application.volatile bu this is dangerous and has caused me problems in the past. Is there and easier way to do this either by formula or better designe UDF??? Cheers, B -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last valid value in array
Use
=INDEX(1:1, MATCH(9.999999E+307,1:1)) and drag the formula down.. -- Regards Juan Pablo González "bcmiller " wrote in message ... Hi, I have written a UDF to search through a range to find the last valid value eg: a b c d e 1 1 5 5 2 6 3 1 7 8 In these examples 5, 6 and 8 would be the last valid enties Basically I transfer the range to an array and search from the end back until I find a value. The problem is that the values don't update when data is changed. I had thought about using application.volatile but this is dangerous and has caused me problems in the past. Is there and easier way to do this either by formula or better designed UDF??? Cheers, BC --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last valid value in array
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to do - B2 is only valid if B1 is zero | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
'reference is not valid' | Excel Discussion (Misc queries) | |||
Reference is not valid | Excel Worksheet Functions | |||
Array formulas are not valid in merged cells. | Excel Programming |