View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
nastech nastech is offline
external usenet poster
 
Posts: 383
Default Formula structure, maybe not parenthesis, CELL()?

060925 Formula structure, maybe not parenthesis, CELL()?

hi, have formula that is combo hyperlink to (this case) min value in column
range.
have start & stop rows selected in original formula that works.
(both branches are same range att, doesn't matter now)

trying to double up with simple choice: IF($BQ$11="x",
then use 1st, or 2nd range; chose 2 branch method (2 areas).. instead of
subdividing...

PROBLEM:
sorry long formula :) , but actually should be simple to drop in cell: BR11
and see why can not do: cntrl-shift-enter for an array.
believe have parenthesis correct, not sure of problem. thanks

=HYPERLINK(IF($BQ$11="x",
IF(ISNA(INDEX(ROW(BR$393:BR$408)-ROW(BR$393)+1,INDEX(ROW(BR$393:BR$408),MATCH(MIN(B R$393:BR$408),BR$393:BR$408,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",BR11),"",""),ROW(),"")&INDEX (ROW(BR$393:BR$408),MATCH(MIN(BR$393:BR$408),BR$39 3:BR$408,0))),$V$2,0))),
IF(ISNA(INDEX(ROW(BR$393:BR$408)-ROW(BR$393)+1,INDEX(ROW(BR$393:BR$408),MATCH(MIN(B R$393:BR$408),BR$393:BR$408,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",BR11),"",""),ROW(),"")&INDEX (ROW(BR$393:BR$408),MATCH(MIN(BR$393:BR$408),BR$39 3:BR$408,0))),$V$2,0)))),
IF($BQ$11="x",MIN(IF(ISNUMBER(BR$393:BR$408),BR$39 3:BR$408,1E+100)),MIN(IF(ISNUMBER(BR$393:BR$408),B R$393:BR$408,1E+100))))