dynamic named range in chart becomes absolute

Hi,



Can someone tell me how I can set the data for a chart with named dynamic ranges so that these ranges are not translated absolute by Excel ??


I've tried to set a dynamic named range as the source/series/formula of a chart through a couple of ways but apparently it gets "translated" to the "current" absolute range. I would rather have it relative/dynamic so that the chart changes when data in the range changes (more, less items in the range).

The named ranges are correct and dynamic, I can test that by printing count of the range and play with the range.

at first I tried to set the data via (right click chart > select data) chart data range, but apparently that is the easiest way to get absolute ranges.

I tried setting via seriecollection.values (for instance

.SeriesCollection(1).Values = Range("maten_ola!rngBovengrensVerdelingTopicOla")

and when I Msgbox the formula for the series it is translated to the current range (for instance maten_ola!D2:D10), and changes to the range are clearly "lost".



I was hoping to have the chart configured with dynamic ranges so that it could be used flexible with external data without users or application (on import do ...) "resetting" the values with the dynamic range but I guess it should be possible to have the dynamic range dynamic (relative not absolute).

It's driving me nuts ;-)

YES !! I think it does ;-)

thank you very very (very!) much chemist.