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