G
Guest
060716 MATCH function, exclusion question
hi, trying to use MATCH to find the first instance of items, but want to
exlude the top of document where there may be one or more of the "instances-
item erors"
the following does not work for receiving a #VALUE error.
e.g.:
AND(ROW()>=$B$56,MATCH("dd",$D$1:$D$1303,0))
full example in use: is for hyperlink to first instance:
=HYPERLINK(IF(ISNA(MATCH("X",$D$1:$D$1303,10000)),""
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W12),"$",""),ROW(),"")&
AND(ROW()>=$B$56,MATCH("dd",$D$1:$D$1303,0))),$W$1,0))),"dd")
notes:
- remove AND(ROW()>=$B$56, and following: ")"
- to work: W12, is column where want link to end up, 12 is row this
formula resides. thanks
hi, trying to use MATCH to find the first instance of items, but want to
exlude the top of document where there may be one or more of the "instances-
item erors"
the following does not work for receiving a #VALUE error.
e.g.:
AND(ROW()>=$B$56,MATCH("dd",$D$1:$D$1303,0))
full example in use: is for hyperlink to first instance:
=HYPERLINK(IF(ISNA(MATCH("X",$D$1:$D$1303,10000)),""
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W12),"$",""),ROW(),"")&
AND(ROW()>=$B$56,MATCH("dd",$D$1:$D$1303,0))),$W$1,0))),"dd")
notes:
- remove AND(ROW()>=$B$56, and following: ")"
- to work: W12, is column where want link to end up, 12 is row this
formula resides. thanks