Prev: date
Next: excel separation
From: Dan on 8 May 2010 13:20
hi, I have pieces of formula's that might help to figure out how to find
where an error is located (by column & row). thanks in advance.

examples include:
a - find exact character "X" row number (friendly name in hyperlink)
b - hyperlink to that row.. column
c - formula to detect error in a range
d - item trying to modify to find error location

A:
=IF(ISNA(INDEX(ROW($B$2:$N$7)-ROW($A$2),MATCH(TRUE,EXACT($B$2:$N$7,"X"),0))),
INDEX(ROW($B$2:$N$7),MATCH(TRUE,EXACT($B$2:$N$7,"X"),0)),"")


C: (this works for detecting error, I NEED TO ID CELL ERROR LOCATED IN)
=IF(SUMPRODUCT(--ISERROR($B$2:$N$7))>0,"error","noerror")


D: (idea, trying to insert: ISERROR ??, instead of Exact "X")
=IF(ISNA(INDEX(ROW($B$2:$N$7)-ROW($A$2),MATCH(TRUE,EXACT($B$2:$N$7,"X"),0))),
INDEX(ROW($B$2:$N$7),MATCH(TRUE,EXACT($B$2:$N$7,"X"),0)),"")


other: do not need hyplink in my problem here, info only)
B: (array: commit by cntrl-alt-enter; this formula used to solve find X
in diff location / column)
=HYPERLINK(IF(AND(
ISNA(INDEX(ROW($BX$355:$BX$1944)-ROW($BX$355),MATCH(TRUE,EXACT($BX$355:$BX$1944,"X"),0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$EL17),"$",""),ROW(),"")&INDEX(ROW($BX$355:$BX$1944),MATCH(TRUE,EXACT($BX$355:$BX$1944,"X"),0))),$AB$3,0))),
IF($EL$18<>"< top",IF(AND(
ISNA(INDEX(ROW($BX$355:$BX$1944)-ROW($BX$355),MATCH(TRUE,EXACT($BX$355:$BX$1944,"X"),0)))),"",
INDEX(ROW($BX$355:$BX$1944),MATCH(TRUE,EXACT($BX$355:$BX$1944,"X"),0))),"delist'd"))

 | 
Pages: 1
Prev: date
Next: excel separation