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:
Name1xyzName2xyzI 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?
Bookmarks