Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setup problem for hyperlink in Excel ! | Excel Worksheet Functions | |||
Setup problem for hyperlink in Excel ! | Excel Discussion (Misc queries) | |||
How do I setup a formula to insert the rate? | Excel Worksheet Functions | |||
How to setup a hyperlink to an indirect function? | Excel Worksheet Functions | |||
how to setup formula? | Excel Worksheet Functions |