N
nastech
Have a large document, want to find where error is asap & hyperlink to it?
also below, example of in-document link, to get you back to same spot,
every-time.
ANSWER TO: Find Error result in column, where calculated (pick a start
point & exclude a range), Hyperlink to it (with row number as friendly name
in hyperlink)
=HYPERLINK(IF(AND(
ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X"),0))),
ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X"),0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$W14),"$",""),ROW(),"")&INDEX(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$AX$1168,"X"),0))),$W$1,0))),
IF($BX$15>0,IF(AND(
ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X"),0))),
ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X"),0)))),"",
INDEX(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$AX$1168,"X"),0))),"-"))
note: $W14 is the row that that formula resides in, W is column you want
cursor to end up in.
Quantity of Errors:
=IF($G$7="x",SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x")),
SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"),--($G$173:$G$540<>"x")))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x"),--($G$567:$G$1168<>"x"))
SAMPLE: Test for errors:
=IF(AND(S9<>".",OR($G$7="x",G9<>"x"),OR(CP9={"",0})),"X",
OTHER:
=HYPERLINK("#"&CELL("address",OFFSET($A$69,-1,1)),"top")
IN-DOCUMENT Hyperlink: GETS YOU BACK TO EXACT SAME SPOT, EVERYTIME.
=HYPERLINK(IF(ROW($A$607)<(ROW($A543)-($W$1/2)),"#"&CELL("address",OFFSET($A$607,-1,1)),"#"&CELL("address",OFFSET($A$607,$W$1,1))),"A")
($A50 is row formula resides in)
Utility: $W$1 method for screen size / from freezepane (any # minus size
of freezepane = rows showing)
=48-CELL("row",$A$17)
also below, example of in-document link, to get you back to same spot,
every-time.
ANSWER TO: Find Error result in column, where calculated (pick a start
point & exclude a range), Hyperlink to it (with row number as friendly name
in hyperlink)
=HYPERLINK(IF(AND(
ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X"),0))),
ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X"),0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$W14),"$",""),ROW(),"")&INDEX(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$AX$1168,"X"),0))),$W$1,0))),
IF($BX$15>0,IF(AND(
ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X"),0))),
ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X"),0)))),"",
INDEX(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$AX$1168,"X"),0))),"-"))
note: $W14 is the row that that formula resides in, W is column you want
cursor to end up in.
Quantity of Errors:
=IF($G$7="x",SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x")),
SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"),--($G$173:$G$540<>"x")))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x"),--($G$567:$G$1168<>"x"))
SAMPLE: Test for errors:
=IF(AND(S9<>".",OR($G$7="x",G9<>"x"),OR(CP9={"",0})),"X",
OTHER:
=HYPERLINK("#"&CELL("address",OFFSET($A$69,-1,1)),"top")
IN-DOCUMENT Hyperlink: GETS YOU BACK TO EXACT SAME SPOT, EVERYTIME.
=HYPERLINK(IF(ROW($A$607)<(ROW($A543)-($W$1/2)),"#"&CELL("address",OFFSET($A$607,-1,1)),"#"&CELL("address",OFFSET($A$607,$W$1,1))),"A")
($A50 is row formula resides in)
Utility: $W$1 method for screen size / from freezepane (any # minus size
of freezepane = rows showing)
=48-CELL("row",$A$17)