+ Reply to Thread
Results 1 to 5 of 5

INDIRECT and dynamic ranges

  1. #1
    Forum Contributor
    Join Date
    09-19-2007
    Posts
    115

    INDIRECT and dynamic ranges

    i tried to use the INDIRECT function to refer to a cell that contains the name of a dynamic range, to only discover that INDIRECT and dynamic ranges do no work! any suggestions for a work-around in a formula?

    so far i've tried to determine the starting and ending cells of the range for a particular element (eg interest) as detailed in the attached example by determine starting cell, count fields to determine ending cell then using the ADDRESS function to convert it into a R1 format (yellow cells), then INDIRECT to the cell containing the "range" (green cell), yet this seems cumbersome.
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    I'd normally try to avoid INDIRECT, ADDRESS etc. in favour of INDEX. You could use this formula in I4 which references the elements in B1 and E8

    =SUMIF(INDEX(H14:M23,MATCH(E8,G14:G23,0),0),H1, INDEX(H14:M23,MATCH(B1,G14:G23,0),0))

  3. #3
    Forum Contributor
    Join Date
    09-19-2007
    Posts
    115
    so that will handle the dynamic range? i'm trying to understand how the formula evaluates the ranges yet i can't follow it :o(

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    The ranges within the SUMIF are dynamic based on the values in E8 and B1, for example:

    INDEX(H14:M23,MATCH(B1,G14:G23,0),0))

    This takes the range H14:M23 and selects a specific row based on matching B1 in the range G14:G23. If you change the value in B1 you'll get a different row so the SUMIF will return a different value.

    INDEX/MATCH is usually a more efficient way to do this than using INDIRECT/ADDRESS but is often overlooked; possibly because no part of the above formula, on it's own, explicitly returns the range as a string, but it works when used in place of a range in a SUMIF/SUMPRODUCT type formula

  5. #5
    Forum Contributor
    Join Date
    09-19-2007
    Posts
    115
    thanks daddylonglegs.

    i evaluated the formula to understand what the INDEX, MATCH and INDEX functions achieve and how it does it, yet since it doesn't resolve like a SUMPRODUCT i can't readily appreciate the components of the formula. thanks for explaining the nature of the formula and the components.

    so, if i have numerous ranges (there are something like 400 accts and 40 groups for example) i'd simply define the common ranges for accts and groups and data ranges in a table and refer to the cell from that table in the formula?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1