help with Hyperlink formula setup
 

hi, have an extensive example of a hyperlink, but should be easy to see
portions, with line returns to help separate sections.. thanks in advance

version that works for "dn" shortcut, (to next copy of this link) is in fact
what the 2nd (non-workin) version is keying off.
note: cntrl-shift-enter array, $z$3 is number of rows to move down (size
of screen), placed in row 141

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT141:$AT$123 2,1,0))-ROW(OFFSET($AT141,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0)))," ",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($AT141:$AT$1232,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),$ Z$3,1))),"dn")


Modified version that is not quite right, can not enter / am probably using
functions / variables not needed ??? thanks, non-working example is:
note: placed in row 141, column to right, AU

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141 ,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),-1,1))),"u")


base formula working with, that seems to be working: row 141 is not
included in query, previous / last occurance of "dn" is correctly identified.

=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))

help with Hyperlink formula setup
 

Why don't you select the cell that has errors and use Tools Menu - Mormula
Auditing - Evalute Formula?


"nastech" wrote:

hi, have an extensive example of a hyperlink, but should be easy to see
portions, with line returns to help separate sections.. thanks in advance

version that works for "dn" shortcut, (to next copy of this link) is in fact
what the 2nd (non-workin) version is keying off.
note: cntrl-shift-enter array, $z$3 is number of rows to move down (size
of screen), placed in row 141

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT141:$AT$123 2,1,0))-ROW(OFFSET($AT141,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0)))," ",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($AT141:$AT$1232,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),$ Z$3,1))),"dn")


Modified version that is not quite right, can not enter / am probably using
functions / variables not needed ??? thanks, non-working example is:
note: placed in row 141, column to right, AU

=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141 ,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$A141),"$",""),ROW(),"")&IND EX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),-1,1))),"u")


base formula working with, that seems to be working: row 141 is not
included in query, previous / last occurance of "dn" is correctly identified.

=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))