Circular Reference / OFFSET

[Guest]

hi, trying to make a hyperlink work, that is used "in" document where
sub-destinations are all -progressively id'd as "dn" by the friendly name in
the hyperlink.

the problem is the reference to the column / cell the formula currently
occupies.
answer would be to change the following formula to be offset +1 for the 4
copies of the range. guesse might have to remove the "$" sign from the
beginning of the range??.. to make it dynamic.

purpose: to be able to paste formula down the document, and be able to
follow "dn" links to sub sections.

is it possible to add the offset to the range in this manner? thanks.
note: array (cntrl-shift-enter)

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

 

[Guest]

think have partial answer, link works, but still get the circular reference:
(using offset in the last variable does not take; don't know if correct
idea anyways)

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


(might still need to remove some $ signs later)

 

[Guest]

would seem to be one step closer, 2 changes, but with last variable changed
from 183 to 184, gets rid of circular reference, is not what desired; where
offset does not take hold in that last variable, that is where need help
with.. unless other answer, thanks.


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


(cntrl-shift-enter / may need to remove some $ signs later)

 

[Guest]

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