Answer to: test/ hyperlink to an Error in a column

[Guest]

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)

 

[Guest]

missed changing in doc hyperlink:
($A50 is row formula resides in, have say is $A543, in this example).
thanks.

 

[Guest]

use: Ctrl-Shift-Enter, to enter array formula's

 

[Guest]

single item search, 1 range, display destination row: (test for empty cell
in column)
=HYPERLINK(IF(ISNA(INDEX(ROW($S$57:$S$1168)-ROW($S$57)+1,MATCH(TRUE,EXACT(($S$57:$S$1168),""),0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$S14),"$",""),ROW(),"")&INDEX(ROW($S$57:$S$1168),MATCH(TRUE,EXACT($S$57:$S$1168,""),0))),$W$1,0))),
IF(ISNA(INDEX(ROW($S$57:$S$1168)-ROW($S$57)+1,MATCH(TRUE,EXACT(($S$57:$S$1168),""),0))),"",INDEX(ROW($S$57:$S$1168),MATCH(TRUE,EXACT($S$57:$S$1168,""),0))))

$S14 is row formula resides in, S is column you want cursor to end up in.
use Cntrl-Shift-Enter, to enter array formula's
=SUMPRODUCT(--(LEFT($S$57:$S$1168,1)="")) number of rows with empty cells
that column