Help - Dynamic Chart is unstable, likely due to volatile named ranges - Need suggestions

[shellecj]

Can someone please assist by taking a look at the attached workbook to offer suggestions for improvement? For this particular chart I have two combo box drop down selections, one for the start month & year and the 2nd combo drop down box is for the user to select the month & year for the desired ending range. I have established the dynamic named range for the ending month-year selection to start exactly one month after the selected start month, to avoid inadvertently selecting a month that does not fall after the first month (e.g. Sept 2014 through Nov 2013). All my named ranges seem to work fine and hone in on the desired ranges however when playing around with the start and end selections for testing the chart, the workbook often becomes unstable and crashes. I'm guessing due to the formula's within the named range being volatile functions. I've used these type of formulas for dynamic named ranges for several years now (even in conjunction with combo box list fill ranges) but this is the first time I've run into this problem.

Any suggestions, feedback or ideas would be greatly appreciated!

a sample test workbook attached but please note that I have the calculation mode set to manual so when testing you will need to change to automatic calc or manual update (F9)

Thanks!

dynamic chart.xlsx

[TMS]

This particular workbook has Calculation set to Manual. Could that be the/a problem?


Regards,TMS

[shellecj]

I turned it to manual so that I could at least make edits to the named range formulas (which I did find some errors in the references and offsets, which I fixed) but without turning the calculation to manual I couldn't even stay in the workbook to edit the ranges long enough before it crashed. It seems I could pick two months (beginning and end) and hit F9 to update the workbook and the chart updates fine but when I turn the calculation mode back to automatic and play around with the selections, Excel crashes??

[martindwilson]

i cant get my head around all those offsett, have you considered using index for the dynamic ranges?
http://www.excelhero.com/blog/2011/0...ing-index.html

[TMS]

Ditto mdw. Offsets of Offsets of Offsets ...

I like the way the second list is dependent on the first but just got lost in the detail ...

[shellecj]

I thought about that but just couldn't figure out a solution with how to do that for my example??

For example the named range for the end month selection is offset one down in the list because I want to start the range starting with the next consecutive month. And for the chart range it offsets with the starting range, moves one row over and makes the range 2 rows wide. Click on the range for the Sale_Chart_Date_Range and you'll see what I mean.

I definitely am up to suggestions and ideas though if you can help me come up with a different way to dynamically establish my range using just index or any other non volatile function

Thanks!

[shellecj]

Are any of you having issues with the workbook crashing when changing the calc mode to automatic and playing around with changing the dates? I was just assuming that this issue was caused due to the volatility of using offset (though again never had an issue w/ using this function before) but not sure if this is the case or not? If so then I guess it finally caught up with me! ha!

[martindwilson]

no because a i cant see any formulas or dropdowns in that workbook ,only the named ranges

[TMS]

Didn't crash for me. I can see formulae and drop down/combo box.

[shellecj]

the formulas are in the named ranges (CTRL F3), which the chart data references. Try turning to automatic and play around with the selections by trying different combinations maybe?