trying to make the ROW() thing work, because may have settings, above in that
column, such as "u" for up, that otherwise conflicts with other work samle we
did, for e.g.: (not working) thanks.

=HYPERLINK(IF(OR(ROW()<=$BC$15,ROW()=$BH$15,ISNA( MATCH("X",$AF$1:$AF$1197,0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",W16),"$",""),ROW(),"")&MATCH ("U",$AF$1:$AF$1197,0)),$W$1,0))),"x")


note: found can leave line returns in cells, for working on formula's, does
not seem to interfere with formula.

"Bernie Deitrick" wrote:

Actually, I forgot to change the ,100 to a value higher than 1212 - use 10000 or some other large
value in this expression (both places)

MIN(IF(ISERROR(AF1:AF1210),ROW(AF1:AF1210),100))
should be something like
MIN(IF(ISERROR(AF1:AF1210),ROW(AF1:AF1210),100000) )

--
HTH,
Bernie
MS Excel MVP


"nastech" wrote in message
...
If anyone cares, slight modification made to exclude lines greater than or
<less than certain values: thanks again

FIND ERROR IN COLUMN: (by Bernie Deitrick, XL MVP) Cntrl-Shift-Enter bc15
refers to: =CELL("row",$A$68)
=HYPERLINK(IF(OR(ROW()<=$BC$15,ROW()=$BH$15,ISNA( MIN(IF(ISERROR(AF1:AF1210),ROW(AF1:AF1210),100)))) ,"","#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE( SUBSTITUTE(CELL("address",AF15),"$",""),ROW(),"")& MIN(IF(ISERROR(AF1:AF1212),ROW(AF1:AF1212),100))), $W$1,0))),"X")


XXXXXXXXXXXXXXXXXXXXXXXXX

"Bernie Deitrick" wrote:

Array enter - using Ctrl-Shift-Enter

=HYPERLINK(IF(ISNA(MIN(IF(ISERROR(AU1:AU1109),ROW( AU1:AU1109),100))),"","#"&CELL("address",OFFSET(IN DIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",W14)," $",""),ROW(),"")&MIN(IF(ISERROR(AU1:AU1109),ROW(AU 1:AU1109),100))),$W$1,0))),"Error")

HTH,
Bernie
MS Excel MVP


"nastech" wrote in message
...
Hi, was wondering how to include, if an error exists in a column.. was trying
iserror & #div/0! thanks, using: for find "x"

=HYPERLINK(IF(ISNA(MATCH("X",$AU$1:$AU$1109,0)),"" ,
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",W14),"$",""),ROW(),"")&MATCH ("X",$AU$1:$AU$1109,0)),$W$1,0))),"X")



"Bernie Deitrick" wrote:

=HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(IF(I SNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUTE(SUB STITUTE(CELL("address",W14),"$",""),ROW(),"")&MATC H("X",$AU$1:$AU$1097,0))),0,0)),"x")

HTH,
Bernie
MS Excel MVP


"nastech" wrote in message
...
060711 search column, hyperlink, offset, substitute, match (omg)

trying to get a hyperlink to go to designated errors (column where "X"
appears if there is an error). using the last 2 items, it works as a worker
cell & hyperlink refering to it.

trying to get this to work, (trying to combine the bottom 2 items), get "too
few arguments". (note: to work, W14 is "column you want link to go to, & 14
is the line that formula is currently in). thanks.

=HYPERLINK("#"&CELL("address",OFFSET(SUBSTITUTE(SU BSTITUTE(CELL("address",W14),"$",""),ROW(),"")&MAT CH("X",$AU$1:$AU$1097,0),0,0)),"x")
trying

these work separately
=HYPERLINK("#"&CELL("address",OFFSET(INDIRECT($CH$ 14),0,0)),"x") works
=IF(ISNA(MATCH("X",$AU$1:$AU$1097,0)),"",SUBSTITUT E(SUBSTITUTE(CELL("address",W14),"$",""),ROW(),"") &MATCH("X",$AU$1:$AU$1097,0))
works