D
Dan
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"))
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"))