hi, trying to see why formula does not work. the first example works except
for wrong use of variable at end (184 needs to be 183 with an offfset);

purpose: hyperlink "dn" to subsections of sheet. use: cntrl-shift-enter,
for array.

=HYPERLINK(IF(ISNA(
INDEX(ROW(OFFSET($AT183:$AT$1156,1,0))-ROW(OFFSET($AT183,1,0)),
MATCH(TRUE,EXACT(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,EXACT($AT184:$AT$1156,"dn"),0))),$Z$3,0 ))),"dn")

the next example would be the answer, but it will not accept as a valid
formula.
(added the OFFSET spoke of, and changed 183 to 184).

=HYPERLINK(IF(ISNA(
INDEX(ROW(OFFSET($AT183:$AT$1156,1,0))-ROW(OFFSET($AT183,1,0)),
MATCH(TRUE,EXACT(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,EXACT(OFFSET($AT183:$AT$1156,1,0),"dn") ,0))),$Z$3,0))),"dn")

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).




"nastech" wrote:

hi, trying to see why formula does not work. the first example works except
for wrong use of variable at end (184 needs to be 183 with an offfset);

purpose: hyperlink "dn" to subsections of sheet. use: cntrl-shift-enter,
for array.

=HYPERLINK(IF(ISNA(
INDEX(ROW(OFFSET($AT183:$AT$1156,1,0))-ROW(OFFSET($AT183,1,0)),
MATCH(TRUE,EXACT(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,EXACT($AT184:$AT$1156,"dn"),0))),$Z$3,0 ))),"dn")

the next example would be the answer, but it will not accept as a valid
formula.
(added the OFFSET spoke of, and changed 183 to 184).

=HYPERLINK(IF(ISNA(
INDEX(ROW(OFFSET($AT183:$AT$1156,1,0))-ROW(OFFSET($AT183,1,0)),
MATCH(TRUE,EXACT(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,EXACT(OFFSET($AT183:$AT$1156,1,0),"dn") ,0))),$Z$3,0))),"dn")