Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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)))) |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
original formula that works: (can keep line return format in cell for easy
edit/view) =HYPERLINK(IF(ISNA(INDEX(ROW(BR$391:BR$406)-ROW(BR$391)+1,INDEX(ROW(BR$391:BR$406),MATCH(MIN(B R$391:BR$406),BR$391:BR$406,0)))),"", "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",BR11),"",""),ROW(),"")&INDEX (ROW(BR$391:BR$406),MATCH(MIN(BR$391:BR$406),BR$39 1:BR$406,0))),$V$2,0))), MIN(IF(ISNUMBER(BR$391:BR$406),BR$391:BR$406,1E+10 0))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
assign formula to another cell | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel |