Define 2 named ranges (Formula Tab > Name Manager > New):
-----------------------------------------------------------------------------
JobSat_Score
=OFFSET(Presentation!$B$7,0,0,COUNTA(Presentation!$B:$B)-5,1)
WorkLife_Score
=OFFSET(Presentation!$C$7,0,0,COUNTA(Presentation!$C:$C)-1,1)
Change the parameters according to your pivot design.
Insert a scatter plot chart.
Add a series:
Series X values: ='Refresh Data
Labels.xlsm'!JobSat_Score
Series Y values: ='Refresh Data Labels.xlsm'!WorkLife_Score
Adjust chart axis as per your requirement.
For adding and refreshing data labels dynamically write this
procedure and call it on a button click.
Sub refreshLabels()
On Error Resume
Next
Dim counter As
Integer, xValueFormula As String
Application.ScreenUpdating = False
ActiveSheet.ChartObjects(1).Activate
xValueFormula =
ActiveChart.SeriesCollection(1).Formula
'getting the
series name for X values
xValueFormula =
Mid(xValueFormula, InStr(1, xValueFormula, ",'", vbTextCompare) + 1)
xValueFormula = Left(xValueFormula, InStr(1,
xValueFormula, ",'", vbTextCompare) - 1)
'Attach a label to
each data point in the chart
For counter = 1 To
Range(xValueFormula).Cells.Count
ActiveChart.SeriesCollection(1).Points(counter).HasDataLabel = False
ActiveChart.SeriesCollection(1).Points(counter).HasDataLabel = True
ActiveChart.SeriesCollection(1).Points(counter).DataLabel.Text =
Range(xValueFormula).Cells(counter, 1).Offset(0, -1).Value
Next counter
End Sub
DATA USED FOR EXAMPLE
Please put your thoughts and comments below so that I can improve.
THANKS EVERYONE.
SUBHAJIT.
