Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF(ISNA.. or ISERROR? question..
p.s.: looking for any error..? most common: N/A, Div0
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formating iserror isna | Excel Discussion (Misc queries) | |||
Use both ISNA and ISERROR in a long formula | Excel Worksheet Functions | |||
ISNA question | Excel Discussion (Misc queries) | |||
So close and yet so far (IF ISNA question) | Excel Discussion (Misc queries) | |||
ISNA question | Excel Worksheet Functions |