ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
 

Use of hyperlink formula's can allow much movement around a sheet, some used
in header would move down to max or min values in a sheet will submit example
at end of this post.

the following works for use "in-document" to move progressively down a
column, to: in this example, the next occurrance of "dn" (generated by this
formula).
$Z$3 is the fixed cell where reference to how many rows I have showing in my
sheet, also included. use cntrl-shift-enter the formula, subsequent
copy-pastes will merely work. "DOWN" formula is:

hi, think have found what would be 90% the answer:
(while combining old formula's), found that deleting item for: EXACT, could
get formula to work. If there is an answer for use of exact, will still take
that response.

Formula works as:
=HYPERLINK(IF(ISNA(
INDEX(ROW(OFFSET($AT183:$AT$1156,1,0))-ROW(OFFSET($AT183,1,0)),
MATCH(TRUE,OFFSET($AT183:$AT$1156,1,0)="dn",0)))," ",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$AT183),"$",""),ROW(),"")&
INDEX(ROW(OFFSET($AT183:$AT$1156,1,0)),
MATCH(TRUE,OFFSET($AT183:$AT$1156,1,0)="dn",0))),$ Z$3,0))),"dn")

note: $Z$3 is a work cell for using amount of rows avail. minus number of
rows in "Freeze Pane" header, such as: =ROW($A$51)-ROW($A$17)

you can further externalize the value "For" $A$51 (e.g. in $AQ$3):
=$AQ$3-CELL("row",$A$17)
step further: externalize number of rows you might hide / collapse in header:
($Y$3-CELL("row",$A$17)) & y3: =$AQ$3+$AT$3

(have fun figuring that out).

XXXXXXXXXXXX
use: cntrl-shift-enter for array formula's
MAX Hyperlink example, I use in header / freeze pane of document:

=HYPERLINK(IF(ISNA(INDEX(ROW(CS$175:CS$723)-ROW(CS$175),
INDEX(ROW(CS$175:CS$723),MATCH(MAX(CS$175:CS$723), CS$175:CS$723,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",CS12),"",""),ROW(),"")&
INDEX(ROW(CS$175:CS$723),MATCH(MAX(CS$175:CS$723), CS$175:CS$723,0))),$Z$3,0))),MAX(IF(ISNUMBER(CS$17 5:CS$723),CS$175:CS$723,-1E+100)))

ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
 

Example of: find a (error response) character in a column, use of a
"dead-zone" area, show quantity of "X" 's

=HYPERLINK(IF(AND(
ISNA(INDEX(ROW($BU$175:$BU$883)-ROW($BU$175),MATCH(TRUE,EXACT($BU$175:$BU$883,"X") ,0))),
ISNA(INDEX(ROW($BU$884:$BU$1156)-ROW($BU$884),MATCH(TRUE,EXACT($BU$884:$BU$1156,"X" ),0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$AV13),"$",""),ROW(),"")&IND EX(ROW($BU$175:$BU$1156),MATCH(TRUE,EXACT($BU$175: $BU$1156,"X"),0))),$Z$3,0))),
IF($DD$14<"0 top",IF(AND(
ISNA(INDEX(ROW($BU$175:$BU$883)-ROW($BU$175),MATCH(TRUE,EXACT($BU$175:$BU$883,"X") ,0))),
ISNA(INDEX(ROW($BU$884:$BU$1156)-ROW($BU$884),MATCH(TRUE,EXACT($BU$884:$BU$1156,"X" ),0)))),"",
INDEX(ROW($BU$175:$BU$1156),MATCH(TRUE,EXACT($BU$1 75:$BU$1156,"X"),0))),"delist'd"))

ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
 

I wouldn't call it neat in the true sense of the word
<bg


--

Regards,

Peo Sjoblom




"nastech" wrote in message
...
Example of: find a (error response) character in a column, use of a
"dead-zone" area, show quantity of "X" 's

=HYPERLINK(IF(AND(
ISNA(INDEX(ROW($BU$175:$BU$883)-ROW($BU$175),MATCH(TRUE,EXACT($BU$175:$BU$883,"X") ,0))),
ISNA(INDEX(ROW($BU$884:$BU$1156)-ROW($BU$884),MATCH(TRUE,EXACT($BU$884:$BU$1156,"X" ),0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$AV13),"$",""),ROW(),"")&IND EX(ROW($BU$175:$BU$1156),MATCH(TRUE,EXACT($BU$175: $BU$1156,"X"),0))),$Z$3,0))),
IF($DD$14<"0 top",IF(AND(
ISNA(INDEX(ROW($BU$175:$BU$883)-ROW($BU$175),MATCH(TRUE,EXACT($BU$175:$BU$883,"X") ,0))),
ISNA(INDEX(ROW($BU$884:$BU$1156)-ROW($BU$884),MATCH(TRUE,EXACT($BU$884:$BU$1156,"X" ),0)))),"",
INDEX(ROW($BU$175:$BU$1156),MATCH(TRUE,EXACT($BU$1 75:$BU$1156,"X"),0))),"delist'd"))

ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
 

recommend destination cell (after hyperlink jump) is to an empty column
(e.g.: B)
so if have bad mouse, / bad click, do not accidently paste over data. use
in all examples, this example (in-doc move down sheet)
items changed: address destination either $A, with 0 after $Z$3, changed
to: 1
or use column $B instead of $A..

=HYPERLINK(IF(ISNA(
INDEX(ROW(OFFSET($AT183:$AT$1156,1,0))-ROW(OFFSET($AT183,1,0)),
MATCH(TRUE,OFFSET($AT183:$AT$1156,1,0)="dn",0)))," ",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A183),"$",""),ROW(),"")&
INDEX(ROW(OFFSET($AT183:$AT$1156,1,0)),
MATCH(TRUE,OFFSET($AT183:$AT$1156,1,0)="dn",0))),$ Z$3,1))),"dn")

ANSWER: NEAT TOOL FOR HYPERLINK "DN" DOCUMENT.
 

ouch :) , correction here,, with a 1 after $Z$3