N
nastech
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(SUBSTITUTE(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(SUBSTITUTE(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$175:CS$723),CS$175:CS$723,-1E+100)))
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(SUBSTITUTE(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(SUBSTITUTE(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$175:CS$723),CS$175:CS$723,-1E+100)))
, correction here,, with a 1 after $Z$3