Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula setup / OFFSET problem
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") |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula setup / OFFSET problem
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset VBA Problem | Excel Discussion (Misc queries) | |||
page setup problem | Excel Discussion (Misc queries) | |||
Page Setup problem | New Users to Excel | |||
Offset Problem | New Users to Excel | |||
Equation setup problem | Excel Discussion (Misc queries) |