+ Reply to Thread
Results 1 to 4 of 4

using indirect(address(match())) in the offset function

  1. #1
    Registered User
    Join Date
    08-02-2018
    Location
    Atlanta Georgia
    MS-Off Ver
    Microsoft Office 2017
    Posts
    2

    using indirect(address(match())) in the offset function

    I've been getting pretty crazy with dynamic arrays recently to create auto populating graphs and dashboards. I can't tell if I've hit limitations in excel or if it is an error on my end but for the life of me I cant get this formula to work. I am aware it is ugly and computationally expensive so any other suggestions to accomplish the same thing would be appreciated. So I have a spreadsheet with data in it organized like:

    Name1
    x
    y
    z
    Name2
    x
    y
    z
    I am trying to pull data from a specific section with repetitive subsections and I would really like the reference in the offset function to be dependent on a data validated cell which you choose Name1, Name2... etc. From there the offset function will create an array of the desired data from that section (Specified in D1). My formula looks like:

    OFFSET(INDIRECT(ADDRESS(MATCH($D$1,$A:$A,0),1)),2,1,1,COUNTA(months)

    where months is a named set and is equal to the length of the data set running horizontally.

    I have plugged this in as a formula for a named set and it is correctly generating the array, and when I change the value in D1 in moves the array appropriately. However whenever I try to perform any function on the array like sum or use it in a graph I get errors. Is this a limitation of excel, am I messing something up, or is there a work around?

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: using indirect(address(match())) in the offset function

    Hi redwar. Welcome to the forum.

    Would a source data layout such as this be acceptable? Having a common to each group saves a great deal of time and work.



    A
    B
    1
    2
    Name1
    x
    3
    Name1
    y
    4
    Name1
    z
    5
    6
    Name2
    x
    7
    Name2
    y
    8
    Name2
    z
    Last edited by FlameRetired; 08-02-2018 at 06:31 PM.
    Dave

  3. #3
    Registered User
    Join Date
    08-02-2018
    Location
    Atlanta Georgia
    MS-Off Ver
    Microsoft Office 2017
    Posts
    2

    Re: using indirect(address(match())) in the offset function

    Yes that is the easy solution I came up with as soon as I made my post. Currently using a concatenated data header of the primary and sub categories so I avoid having to match past the first iteration of a header. Thanks!

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: using indirect(address(match())) in the offset function

    So does that mean you have solution to your question?

    If so, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    If not please attach a sample Excel file representative of what the challenge actually is. The following seems to indicate there is more to the real problem than earlier stated:
    ....... concatenated data header of the primary and sub categories ..........
    Please let us know.

    If you are unfamiliar with how to upload:


    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDIRECT and MATCH and OFFSET?
    By rupertlewis in forum Excel General
    Replies: 3
    Last Post: 06-12-2018, 10:49 AM
  2. [SOLVED] Address Match and Indirect formula
    By mahershams in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 11-19-2013, 02:26 AM
  3. Offset, Address & Match
    By veeru_php in forum Excel General
    Replies: 1
    Last Post: 06-28-2011, 03:13 AM
  4. OFFSET+INDIRECT+MATCH formula in VBA.
    By bryenwalt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2010, 04:28 AM
  5. Combining offset, address, and match
    By rhart00 in forum Excel General
    Replies: 2
    Last Post: 04-26-2010, 07:01 PM
  6. Indirect address/match
    By Prcntrygrl in forum Excel General
    Replies: 4
    Last Post: 08-26-2009, 05:05 PM
  7. [SOLVED] Match, Index, Indirect, Offset
    By Mark McDonough in forum Excel General
    Replies: 2
    Last Post: 06-18-2006, 11:55 AM

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