Lets define
BigNum as referring to:
9.99999999999999E+307
That settled...
MATCH(BigNum,X)
where
X is either a definite range or a whole column/row reference.
[1] If one wants to invoke the foregoing in order to define a dynamic range,
X must be numeric.
Dynam, which you mention in your original post and which is defined as referring to
=OFFSET(Sheet7!$C$36,0,0,MATCH(9.9999E+307,Sheet7!$C:$C)-1)
should be formulated a bit differently, assuming that the range in C is numeric:
Define
Size as
=MATCH(BigNum,Sheet7!$C:$C)-ROW(Sheet7!$C$36)+1
Then define
Dynam as:
=OFFSET(Sheet7!$C$36,0,0,Size)
[2] What if the range in column C consists of solely text, including "", i.e., formula blanks and there isn't any other range to use which is numeric...
Define
BigText as:
=REPT("z",255)
Then define
Size as:
=MATCH(BigText,Sheet7!$C:$C)-ROW(Sheet7!$C$36)+1
Dynam is than defined as before.
[3] What if the range in column C consists of text and numbers, including "", i.e., formula blanks and there isn't any numeric range to use...
Then defining
Size as:
Code:
=LOOKUP(BigNum,CHOOSE({1,2,3},
MATCH(BigText,Sheet7!$C:$C),
MATCH(BigNum,Sheet7!$C:$C),
MAX(MATCH(BigText,Sheet7!$C:$C),MATCH(BigNum,Sheet7!$C:$C))))-
ROW(Sheet1!$C$36)+1
is a good idea.
[4] If the range in column C can contain any value, COUNTA would be an appropriate choice
if the range does not contain any in-between empty cells:
The definition of
Size changes to:
=COUNTA(Sheet7!$C:$C)-ROW(Sheet7!$C$36)+1
[5] If the range in column C can contain any value and there is at least an in-between empty cell, the definition of
Size becomes a bit harder and expensive...
Code:
=IF(1-ISBLANK(INDEX(Sheet7!$C:$C,ROWS(Sheet7!$C:$C))),
ROWS(Sheet7!$C:$C),
MATCH(BigNum,1/(1-ISBLANK(OFFSET(INDEX(Sheet7!$C:$C,1),0,0,
ROWS(Sheet7!$C:$C)-1)))))-ROW(Sheet7!$C$36)+1
Note. The idiom
MATCH("*",
X,-1)
can be better avoided in this context. It would yield a wrong result with:
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>
q</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>
a</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="" x:fmla='=""'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>
w</TD></TR></TBODY></TABLE>
where the cell between that of "a" and that of "w" is a formula blank, i.e., "".
Got the error ...............
MATCH(9.9999E+307,$A:$A) ....
will give error if there is not a single numeric value in column A,
Following are details,
I am using OFFSET formula for dynamic range ..... where i expect the MATCH(...) should return the row number of last filled cell in that column.
But what i learnt is, the formula
=MATCH(9.9999E+307,$A:$A) ... will return row of last "Number" in column A
Where formula
=MATCH("*",$A:$A,-1) ... will return row of last char in column A
Now if i want to get last row in column A, char/number anything , then i need to use,
= LOOKUP(9.999E+307,CHOOSE({1,2,3},MATCH("*",$A:$A,-1),MATCH(9.9999E+307,$A:$A),MAX(MATCH("*",$A:$A,-1),MATCH(9.9999E+307,$A:$A))))
Hope that clears it more !!!
Thanks,
hsk
