Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Answer to: test/ hyperlink to an Error in a column
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(SUBS TITUTE(CELL("address",$W14),"$",""),ROW(),"")&INDE X(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$ AX$1168,"X"),0))),$W$1,0))), IF($BX$150,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$1 73:$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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Answer to: test/ hyperlink to an Error in a column
missed changing in doc hyperlink:
($A50 is row formula resides in, have say is $A543, in this example). thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Answer to: test/ hyperlink to an Error in a column
use: Ctrl-Shift-Enter, to enter array formula's
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i multiply two columns | Excel Worksheet Functions | |||
copy/convert column email addresses Hyperlink "mailto:" excel97 | Excel Discussion (Misc queries) | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
how to copy 2350 hyperlink full paths to any column in a worksheet ? | Excel Discussion (Misc queries) |