IF(ISNA.. or ISERROR? question..

[Nastech]

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(SUBSTITUTE(CELL("address",CH15),"$",""),ROW(),"")&INDEX(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(ROW($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$177H$1569)-ROW(DH$177),
INDEX(ROW(DH$177H$1569),MATCH(MAX(DH$177H$1569),DH$177H$1569,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",DH13),"",""),ROW(),"")&
INDEX(ROW(DH$177H$1569),MATCH(MAX(DH$177H$1569),DH$177H$1569,0))),$AA$3,0))),MAX(IF(ISNUMBER(DH$177H$1569),DH$177H$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(SUBSTITUTE(CELL("address",CH18),"$",""),ROW(),"")&INDEX(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(SUBSTITUTE(CELL("address",$AU13),"$",""),ROW(),"")&INDEX(ROW(
INDIRECT($AK$13&$Z$2):$AU$1868),MATCH(TRUE,INDIRECT($AK$13&$Z$2):$AU$1868=$AU13,0))),$AA$3,0))),IF(OR($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))))

 

[Nastech]

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

 

[Nastech]

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(SUBSTITUTE(CELL("address",BE13),"$",""),ROW(),"")&INDEX(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(SUBSTITUTE(CELL("address",BE13),"$",""),ROW(),"")&INDEX(ROW($CH$130:$CH$1868),MATCH(TRUE,ISERROR($CH$130:$CH$1868),0))),$AA$3,0))),"err")

 

[Nastech]

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


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