N
nastech
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(SUBSTITUTE(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(SUBSTITUTE(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")
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(SUBSTITUTE(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(SUBSTITUTE(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")