not null cell within a range
Posted by kevin burrell on August 20, 2001 8:30 AM
i have a Cell range with one cell in that range always containing a value, note only 1 cell will contain a value( varying cells within this range can contain this value so cell is not absolute), i want to capture this value wherever it appears in this range and store that value to an absulute cell range.
Could any one help me,
nb. This value is not an integer so the countif functions cannot be used.
Posted by Aladin Akyurek on August 20, 2001 8:53 AM
Kevin,
I understood as saying that you have a range consisting of two or more consecutive cells (apperently in a column) which contains just one value at any time, and that you want to retrieve this value.
Lets say the range of interest is A2:A10.
Use either of:
=INDEX(A2:A10,SUMPRODUCT((ISNUMBER(A2:A10))*(A2:A10<>0)*(ROW(A2:A10))),COLUMN(A:A)) [ Note that this formula disregards 0 values. ]
=INDEX(A2:A10,MATCH(1E+38,A2:A10)+1) [ +1 is needed because the range starts in A2 ]
Aladin
