The 100 was meant to return a value that is larger than any row number that may be returned by the
IF function in the array formula, so that rows that don't have errors or Xs in them will not be
included in the return.

HTH,
Bernie
MS Excel MVP


"nastech" wrote in message
...
hi, will do that, but what is it for? may figure it out, but...
ran into what "may"? be another problem, am using a header row where totals
of quantities are listed for that column.

that cell receives same error, and hyperlink goes to that "first" error,
instead of what intended. that is what my attempt at isolating ROWs out was
for. Is there a different fix? thanks.

"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