Dynamic Charting

[sherylt13]

Hello, am trying to set up an automated report that includes a dynamic chart, where depending on the population, could include anywhere from 3 to 7 columns. I believe there is a way to create this dynamic set up within the Name Manager, in conjunction with the Series Values, but I always need help with the formulas in defining the names and series values, so that the blank columns are not included within the chart.

In the attached, there are three possible chart scenarios, of which scenario 2 is used, and the chart set up would dictate not including columns E-H so as to not take up real estate within the chart.

Appreciate any assistance, thanks as always!

Sheryl

[stasinek]

Hello,
Rearrange your data as in the "data" sheet and then tweak the pivot table and graph.

Regards

[sherylt13]

Very interesting approach, and creative! I like it, however my challenge is... there are actually at least a dozen different scenarios that are possible, so in this case I cannot account for each of them or I would use this approach. For example, there could be no 7Ps, or no 7Ps and 8Ps, or no 5Ps and 9Ps, etc. So my thought was to use the one chart, which will auto-populate based on the report population brought into this "template" and dynamically chart so that blank cells do not appear in the chart.

Any other creative thoughts?

Sheryl