excel formula syntax problem

  • Hello all,


    I'm trying to substitute a static range in a function with a dynamic one, and I cannot seem to get it right.

    Essentially, I have the following function in one of my cells:

    =INDEX(ServiceFees,MATCH($D$2,_Services!$D$2:$D$3,0),MATCH(D4,_Services!$E$1:$J$1,0))

    I would love to be able to substitute the MATCH function ranges for an actual function as follows:

    $D$2:$D$3

    < to be substituted by the following which essentially gives me the same result, except it's not static >

    ADDRESS(CELL("row",ServiceFeesHdrs)+1,CELL("col",ServiceFeesHdrs)) &":"& ADDRESS(CELL("row",ServiceFeesHdrs)+ROWS(ServiceFeesHdrs)-1,CELL("col",ServiceFeesHdrs))

    The address function gives me precisely the same result as the static range address.

    Any help would be greatly appreciated.



    Cordially,

    Marek

  • ADDRESS(CELL("row",ServiceFeesHdrs)+1,CELL("col",ServiceFeesHdrs)) &":"

    & ADDRESS(CELL("row",ServiceFeesHdrs)+ROWS(ServiceFeesHdrs)-1,CELL("col",ServiceFeesHdrs))


    This gives the text representation of a range within the range named ServiceFeesHdrs, but that's not a reference to that range. You could wrap this inside INDIRECT, but that'd mean an unnecessary use of the volatile function INDIRECT. Better to learn how to use the range operator : .


    INDEX(ServiceFeesHdrs,2,1):INDEX(ServiceFeesHdrs,ROWS(ServiceFeesHdrs),1)


    This is a reference to the subrange within ServiceFeesHdrs, and there are no volatile function calls. You could then replace _Services!$D$2:$D$3 in your INDEX formula with the expression above. That said, if _Services!$D$1 (presumably the top-left cell in ServiceFeesHdrs) were blank or was unlike the other cell values in D2:D3, you could use MATCH($D$2,INDEX(ServiceFeesHdrs,0,1),0)-1 as the 2nd argument in your INDEX formulas.


    Tangent: there's NEVER a good reason to use INDIRECT(ADDRESS(...)) or ADDRESS(...) itself unless the worksheet portion of the range address would be variable. Even then there are alternatives which avoid using volatile functions. ADDRESS is a function which appears useful but generally causes more problems than it solves. The function analog to using merged cells.