Data label of last point only

[Neil Jimack]

Hi,

I have several charts displaying cumulative monthly data.
How do I display the value of the most recent month next
to the line, so that it updates every month?
i.e. In period 5, the line runs from periods 1 to 5 and I
want the period 5 figure displayed. Next month, the line
will run from Periods 1 to 6, so I want the period 6
figure displayed.

Any assistance greatly appreciated!

[Jon Peltier]

Neil -

I don't think you can do it automatically, but you can run this macro every
month after updating the data.

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long
If Not ActiveChart Is Nothing Then
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
.HasDataLabels = False
nPts = .Points.Count
mySrs.Points(nPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=False
mySrs.Points(nPts).DataLabel.Text = mySrs.Values(nPts)
End With
Next
Else
MsgBox "Select a chart and try again.", vbExclamation, _
"No Chart Selected"
End If
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <1613601c25a6e$ecec3280$39ef2ecf@TKMSFTNGXA08>, Neil Jimack
said...

[Jon Peltier]

Thought of an automatic way, without VBA. If your data is in A1:Bwhatever
of a sheet named "Sheet1", with non-numeric labels in A1:B1, put "Last X"
into C1 and "Last Y" in D1. Put this formula into C2, and drag it across to
D2:

=OFFSET(A1,COUNT(A:A),0)

This gives you the last value in columns A and B.

Select and copy C1:D2, select your chart, and do Edit menu > Paste Special as
a New Series, Categories in First Column, Labels in First Row. Double click
on the new point, which is superposed on the last point of the first series.
On the Patterns tab, select Nonw for Line and None for Marker. On the Data
Labels tab, select Show Value.

When new data is added, the single point series keeps up with the data, and
its label updates as well.

Note: If this isn't already a dynamic chart, you can fix it so it updates
automatically when the chart updates. Again, this assumes data in A:B.
Press Ctrl-F3 to open the Define Names dialog. Type a name like XVals in the
Name box, and in the Refers To box enter this formula:

=OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A),1)

You can actually do this by typing "=OFFSET(", clicking in cell A1, typing
",1,0,COUNT(", clicking above column A to select the entire column, and
typing "),1)" and clicking Add.

Now type another name in the Name box, like YVals. Enter this formula:

=OFFSET(XVals,0,1)

Click Ok.

Now change the series formula from

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$12,Sheet1!$B$2:$B$12,1)

to this

=SERIES(Sheet1!$B$1,Sheet1!XVals,Sheet1!YVals,1)

Now both the entire series and the last point update as more numbers are
added to the bottom of the data. Don't skip any cells (except A1, if you
want).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <Oefx$HsWCHA.1700@tkmsftngp10>, Jon Peltier said...

[pascual...@hotmail.com]

Double click on the axes that you want the point added and
change the setting on the window to display the last
point. Once you see the last point on the graph. Click the
last point until only the last point is selected
(Sometimes it is trickly, and needs patiences), then right
click and on the window menu choose show value.

>.
>