Data Series Formatting with a Dynamic Range

[Shipoopi]

Hi all,

Hope someone can help. I'm a bit new to all this and I may not be clear so please ask me to elaborate on anything which isn't making sense.

I'm using Excel 2010 and have created a line chart which is edited each week with fresh data continuing the data series hence the need for the dynamic range.

For the last point of the data series I have an arrow head. If I change it for the entire series its a stream of arrows for each point which is not what I need.

The problem I'm encountering is that when fresh data is entered, the next point on the graph that is created has no arrow head and the previous arrow head is still in place.

I could go and manually change this every week so it sounds lazy on my part, however I have seven graphs with two data series per graph.

Is there any way that by using the dynamic range it will continue the format to the next data point.


Any help would be Greatly appreciated

[cyiangou]

To do this neatly, you do need to use a dynamic range so that you can easily determine the last point actually populated.
In the attached (Data Series Formatting with a Dynamic Range.xlsm):

1. See the named calculated range 'TestSeries' and see how it adjusts in size according to the number of entries
2. Also see how the dynamic named range is used instead of a normal range reference in chart data setup.
3. The last point of the series has been set as MarkerStyle = circle.
4. Now enter a new value in the chart data, you'll see the line extend, BUT your point is illustrated - the marker is now on the 2nd last point.
5. Now click the button, and the formatting is restored.

The work for refreshing the series formatting is done by this proc:

Please Login or Register  to view this content.

Aargh. Ignore the extra proc 'Private Sub SetChartData' in the module; I didn't clean up before posting.

[Shipoopi]

this is fantastic and exactly what I'm looking for. The only problem is I have no idea on how to enter the code.

Is there a step by step process of setting up the refresh button or can you advise on where to enter the proc.

Thanks so much for taking the time to look at this