IF(ISNA.. or ISERROR? question..
 

080922: IF(ISNA.. or ISERROR? question..

hi, have a Hyperlink formula that is not working, thought had something that
worked before for finding an error in a column. the following is not working.

=HYPERLINK(IF(ISNA(INDEX(ROW($CH$130:$CH$1868)-ROW($CH$130),MATCH(TRUE,ISERROR($CH$130:$CH$1868), 0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",CH15),"$",""),ROW(),"")&INDE X(ROW($CH$130:$CH$1868),MATCH(TRUE,ISERROR($CH$130 :$CH$1868)),0)),$AA$3,0))),"X")

not sure if setting up right at all, if isna in initial argument would think
will negate what tring to do, but suspect had problem before with some links
jumping past sheet area to blank area after work area.

formula is at fixed position at top in freeze pane area, to jump to an error
for the designated column.
item: ("address",CH15) should be same as cell you place this formula in.
item: $AA$3, fix to number of rows you want offset to jump past "error" to
put at top of screen.


THE FOLLOWING are examples of hyperlink formula's that work:

in document navigation:
=HYPERLINK("#"&CELL("address",OFFSET($A$1196,IF(RO W($A$1196)ROW($A133)-$AA$3/3,$AA$3,-1),1)),"S")

top of doc, navigation, up or dn:
=HYPERLINK("#"&CELL("address",OFFSET($A$1196,-1,1)),"S")
=HYPERLINK("#"&CELL("address",OFFSET($A$1196,$AA$3 ,1)),"S")

find max in col:
=HYPERLINK(IF(ISNA(INDEX(ROW(DH$177:DH$1569)-ROW(DH$177),
INDEX(ROW(DH$177:DH$1569),MATCH(MAX(DH$177:DH$1569 ),DH$177:DH$1569,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",DH13),"",""),ROW(),"")&
INDEX(ROW(DH$177:DH$1569),MATCH(MAX(DH$177:DH$1569 ),DH$177:DH$1569,0))),$AA$3,0))),MAX(IF(ISNUMBER(D H$177:DH$1569),DH$177:DH$1569,-1E+100)))


find specific value in column:
=HYPERLINK(IF(ISNA(INDEX(ROW($CH$402:$CH$1868)-ROW($CH$402),MATCH(TRUE,EXACT($CH$402:$CH$1868,"0" ),0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",CH18),"$",""),ROW(),"")&INDE X(ROW($CH$402:$CH$1868),MATCH(TRUE,EXACT($CH$402:$ CH$1868,"0"),0))),$AA$3,0))),
IF(SUMPRODUCT(--(EXACT(LEFT($CH$402:$CH$1868,1),"0")))<100,"0"&IF( SUMPRODUCT(--(EXACT(LEFT($CH$402:$CH$1868,1),"0")))=10,"
"," "),"")&SUMPRODUCT(--(EXACT(LEFT($CH$402:$CH$1868,1),"0"))))


and similarly, INDIRECT that works:
=HYPERLINK(IF(ISNA(INDEX(ROW(
INDIRECT($AK$13&$Z$2):INDIRECT($AK$13&ROW($A$1868) ))-ROW(INDIRECT($AK$13&$Z$2)),MATCH(TRUE,INDIRECT($AK $13&$Z$2):INDIRECT($AK$13&ROW($A$1868))=$AU13,0))) ,"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$AU13),"$",""),ROW(),"")&IND EX(ROW(
INDIRECT($AK$13&$Z$2):$AU$1868),MATCH(TRUE,INDIREC T($AK$13&$Z$2):$AU$1868=$AU13,0))),$AA$3,0))),IF(O R($AP$14="x",ISNA(INDEX(ROW(
INDIRECT($AK$13&$Z$2):$AU$1868)-ROW(INDIRECT($AK$13&$Z$2)),MATCH(TRUE,INDIRECT($AK $13&$Z$2):$AU$1868=$AU13,0)))),0,
SUMPRODUCT(--(INDIRECT($AK$13&$Z$2):$AU$1868=$AU13))))

IF(ISNA.. or ISERROR? question..
 

p.s.: looking for any error..? most common: N/A, Div0

IF(ISNA.. or ISERROR? question..
 

have found answer to main part of problem: (note: can leave soft
line-returns in cell for easier view, array: cntrl-shift-enter)

=HYPERLINK(IF(ISNA(INDEX(ROW($CH$130:$CH$1868)-ROW($CH$130),MATCH(TRUE,ISERROR($CH$130:$CH$1868), 0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",BE13),"$",""),ROW(),"")&INDE X(ROW($CH$130:$CH$1868),MATCH(TRUE,ISERROR($CH$130 :$CH$1868),0))),$AA$3,0))),"err")

example in other entry for Indirect is for starting row number.. have a
problem finishing this task for getting the following indirect to work, for
columns. if no answer available here, will repost. thanks.

=HYPERLINK(IF(ISNA(INDEX(ROW($CH$130:$CH$1868)-ROW($CH$130),MATCH(TRUE,ISERROR($CH$130:$CH$1868), 0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",BE13),"$",""),ROW(),"")&INDE X(ROW($CH$130:$CH$1868),MATCH(TRUE,ISERROR($CH$130 :$CH$1868),0))),$AA$3,0))),"err")

IF(ISNA.. or ISERROR? question..
 

sorry, mis-paste: 2nd item still working on for indirects not working:


=HYPERLINK("#"&CELL("address",OFFSET(INDIRECT(SUBS TITUTE(SUBSTITUTE(CELL("address",BE13),"$",""),ROW (),"")&
INDEX(ROW(INDIRECT($BP$13)&ROW($A$130):INDIRECT($B P$13)&ROW($A$1868)),MATCH(TRUE,ISERROR(INDIRECT($B P$13)&ROW($A$130):INDIRECT($BP$13)&ROW($A$1868)),0 ))),$AA$3,0)),"err")


"Nastech" wrote:

have found answer to main part of problem: (note: can leave soft
line-returns in cell for easier view, array: cntrl-shift-enter)

=HYPERLINK(IF(ISNA(INDEX(ROW($CH$130:$CH$1868)-ROW($CH$130),MATCH(TRUE,ISERROR($CH$130:$CH$1868), 0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",BE13),"$",""),ROW(),"")&INDE X(ROW($CH$130:$CH$1868),MATCH(TRUE,ISERROR($CH$130 :$CH$1868),0))),$AA$3,0))),"err")

example in other entry for Indirect is for starting row number.. have a
problem finishing this task for getting the following indirect to work, for
columns. if no answer available here, will repost. thanks.

=HYPERLINK(IF(ISNA(INDEX(ROW($CH$130:$CH$1868)-ROW($CH$130),MATCH(TRUE,ISERROR($CH$130:$CH$1868), 0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",BE13),"$",""),ROW(),"")&INDE X(ROW($CH$130:$CH$1868),MATCH(TRUE,ISERROR($CH$130 :$CH$1868),0))),$AA$3,0))),"err")