Resize chart legend automatically

[claws2rip]

Is there a way to dynamically resize the legend of a chart based on the number of items being displayed?

example: I have a pie chart that shows sales per state

Currently I only have sales in 3 states so the legend shows those 3 states.
but lets say after 1 year I have sales in 15 states I would like the legend to increase in height so it can show the 15 states.

The problem I have with making the legend large enough to hold 15 states from the get go (in my example) is if I do that there will be a lot of spacing between each state in the legend

thanks

 

[SkipVought]

hi,

Can't remember if this behavior is in Excel 97-2003, but in 2007+, when I set my chart SOURCE DATA as a Structured Table (Insert > Tables > Table) and then add a chart based on that Structured Table, the SERIES & LEGEND both automatically adjust as data in the Structured Table changes!

I do know in Excel 97-2003, that using Dynamic Named Ranges in the chart cource data AND using those Named Range Names in the Chart Select Data dialog, that SERIES and LEGEND will adjust as well.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]