dynamic pie chart that shows 3 or 4 depending on selection

[Sam Capricci]

Hello, I have a pie chart that either shows 3 items or 4 items depending on the dropdown selection. It is for different locations that can have either two 12 hr shifts or three 8 hr shifts and an "unknown" time for both.

I currently have the "maximum" number of cells selected for the graph and so when 12 hr is selected I have a blank that shows up on the graph (see attached). I'd like it to only show the three 7a-7p and 7p to 7a and unknown when that is selected and the four 7a-3p, 3p-11p, 11p-7a and unknown when 8 hr is selected.

I have tried the "Hidden and Empty Cells" on the select source data option but none of the selections I make work to hide it. It is probably an easy fix but I haven't stumbled on it yet.
I've attached the graph.

Thanks much!

[DBY]

Hello
Using your current set up and formulas, you could create two dynamic named ranges and apply these to your pie chart as the series and category data source. See the attached reply file.

Hope this helps.
DBY

[Sam Capricci]

Sorry DBY, the only thing that came through different from my post was a pie chart with no breakdown, just a "1" and solid blue and no reference info that I can find when I select data.

Overall there are many graphs on the graph page so I'm trying to limit the number of graphs (in case your recommendation involves creating a second graph). And, sorry to say, don't let the complexity of the formulas fool you, I'm probably only moderate skill level in excel unlike a lot of gurus on this site.

SK.

[DBY]

Sorry it's not working for you it works fine on my pc. Not sure what you mean by creating a second graph, it's based on the data in cells D16:E19 that you've created with your formulas. The dynamic ranges merely remove the empty 4th. category at the bottom when selecting the 12 hour shifts.

When you go to edit series, is the following showing in the Series Values Box?

='graph 3_4_Reply.xlsx'!PieValues

If not the names need to be applied to the graph.

DBY

[Sam Capricci]

this is what is in the series values box...
=Sheet1!$E$16:$E$19

[DBY]

It should read as I mentioned above:

Please Login or Register  to view this content.

And for the Axis Label Range:

Please Login or Register  to view this content.

Then it should work if the Named ranges are OK. Check the Name Manager under the Formulas tab, they should refer to the formulas I quote on the sheet.

[Sam Capricci]

I must be doing it incorrectly, it seems to have just changed my shifts to the percents and didn't get rid of the fourth value. See attached. I have not really delt with the name manager before so perhaps I did it incorrectly.

[DBY]

Hi
Yes the chart source data was messed up a little and the named ranges didn't have the Offset formulas applied. I've attached the edited file, which again seems to work fine on my pc.

[Sam Capricci]

I think it is getting closer. I updated the name manager to the offset / countif formulas you provided and when I click on them they seem to highlight the appropriate cells depending on the 8 hr / 12 hr selections. BUT, I still have the fourth value when only three should show up.

BTW, everytime I open yours I get a macro warning and when I say ok, enable this content I then get two alerts that it cannot find PieCategories and it cannot find PieValues.

I attached it again so you can see I'm still getting the fourth value.

[DBY]

Hi
Not sure what to suggest, it works perfectly for me, everything is correct. There are no Macros in the workbook, so I'm not sure why you're getting that warning. As your two series are different lengths the only way I know to adjust them for the chart (without using VBA) is to use dynamic named ranges to alter the length according to the selection made, otherwise the 4th. empty category will remain if you have a static range. Maybe try creating a brand new file and creating the named ranges again.

Sorry
DBY

[DBY]

Hi
Just noticed that you're mixing up the placement of the series data. You're putting:

='graph 3_4.xlsx'!PieValues

in the Series Name instead of the Series Values.

[Sam Capricci]

WOW, that was it. I had to reread it three times but got it right.

THANKS, THANKS, THANKS!!! this has been an issue bugging me for a long time.

[DBY]

Hi
Glad we got it sorted. Dynamic named ranges are very useful but can be difficult to work out when you're not used to them, especially applying them to charts.

DBY