Answer to: test/ hyperlink to an Error in a column
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(SUBS TITUTE(CELL("address",$S14),"$",""),ROW(),"")&INDE X(ROW($S$57:$S$1168),MATCH(TRUE,EXACT($S$57:$S$116 8,""),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
|