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

1. 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:

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. 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

3. 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. Re: using indirect(address(match())) in the offset function

So does that mean you have solution to your question?

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 ..........

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”.
• click “Close window”

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

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