using a single named range (multiple columns of data) to chart multiple series

[luv2glyd]

I have the following formula which extracts an array of data from a different worksheet

Please Login or Register  to view this content.

The above is a named array called open_prices_array. When plotting a chart, instead of selecting a data with my cursor, I type the following in the dialog box:

Please Login or Register  to view this content.

What I'd like to do is have a named range that has 4 columns worth of data rather than one, and than have excel plot all 4 series, but by referring to it by a single name. So rather than typing in currentWorkbookName!open_prices_array for series 1 and currentWorkbookName!high_prices_array for series 2 of the chart and so on, type in currentWorkbookName!ALL_prices_array only once in the chart dialog box and have all 4 columns charted automatically? Is that possible to do somehow?

Thank you.

[brynbaker]

Good luck, strikes me as an unlikely possibility, but I shall watch the thread with interest in case it can be done.

[TS1111]

Not sure if this is still a problem for you, but I may have a solution, albeit a terribly explained one.

I'll explain my issue and how I came to my solution, and hopefully you can derive the solution relative to your data:

This was my data in pivot form, the result of a massive table of data:

Item Wk1 Wk2 Wk3 ... Wk53
A 0 2 4
B 2 3 5
C 0 3 7
D 0 0 0
E 0 0 0

In my case, there would be a maximum of five rows to form this...matrix?... but in the event there were 0's, there were actually blanks. For the columns (# of weeks), it may skip values if there was no activity. Essentially, I created a new matrix off of the pivot table (matrix?) to fill in the blank columns. When I attempted to plot the graph off this with a dynamic range, I had, what I believe is, the same problem you had.

I proceeded to create a system to identify which rows to use (columns in your case) by using IF(LEN()>1,1,0) and creating a Total at the bottom (my Items were longer than 1 character, otherwise blanks are 0, thus 1 and you have an issue with single character items, but you could do =/= 0...). So if 5 items were present, the total was 5; if 2 were present, the total was 2.

After that, I reworked my matrix into a list:

Item Wk Value
A 1 0
A 2 2
A 3 4
...
A 53 x
B 1 2
B 2 3
B 3 5
...
E 53 x

Even if, as in this case, D and E are not present, the list contained spaceholders for the figures. So, if cell A3 of the prelimary matrix is A, and A7 is E, the first 53 values of my list were set to $A$3. Thus, you can set the list up for future values, such as item F, with the last 53-item set = $A$8 and so on... Still a degree of manual work, but it will work for future results. Using this method, you could assign sets of data The Values were quickly captured using a VHLookup, VL(Match).

FINALLY, I created a dynamic named range, i.e. =OFFSET($A$1,0,0,,(IF([SumLen]=0,2,[SumLen]*53)+1),3)
In my case, the SumLen is the aforementioned total to check which columns to use. The 2 is present to keep the pivot table alive in the event no values are present (header + 0s), the 53 represents a year of data (or in your case, all available pricing options), and the 1 is to include the header.

Then, I worked this dynamic range into a Pivot Chart and flopped the axis.

That should give you the ability to have a dynamic, multi-column graph, versus the dynamic-single column data set. You could probably play around with the sum total as a location indicator to add or detract complexity.

Sorry for the poor explanation, but maybe someone can work it out better.