attach labels to secondary x-axis values

[ehamike]

i have a chart that combines an area chart with a scatter chart. I would like to place some labels on the secondary x axis. currently the values on the axis are 0%, 20%, 40%, etc going up as high as neccessary depending on the values being plotted.
There is an associated date that I can calculate to go with each of the %. I would like to be able to print the date right next to the %... like
0% - 10/01/09
20% - 10/15/09
40% - 11/3/09
etc
is this possible?

thx
mike

[Andy Pope]

You can use the data labels of a dummy xy-scatter series to mimic the axis labels.

Use this free addin to link data labels to cells.
http://www.appspro.com/Utilities/ChartLabeler.htm

[ehamike]

Andy
thanks for the reply but I still need a little help

I need the dates to be attached to the % values on the Y-Axis instead of X. X-Axis will always be 0 to 100% by 20% increments
The Y-Axis will always start at 0% and go up in 20% increments but will stop at 120% or greater depending on the data being plotted. I will need to caluclate the date for each of the major units and then attach them after I calculate how many major axis labels there will be.

I tried to copy what I thought you did by adding another series with the X value at 100 for each data point (like where you had value set at 0), but I didn't get it to work.

Also, I have to distribute this to 4 engineers who use this spreadsheet to communicate their project status, so I would rather add the axis labels using VBA if possible so I dont have to get them all to load the utility
Sorry to be such a pain, but can you send me in that direction?
thanks
Mike

[Andy Pope]

You only need the addin to setup the original chart.

Here is code to do it

Sub DummyAxisLabels()
    
    Dim lngItem As Long
    Dim objDLabel As DataLabel
    
    Set rngtext = ActiveSheet.Range("D35:D40")
    With ActiveSheet.ChartObjects("FeverChart").Chart
        With .SeriesCollection("AxisLabels")
            .Values = ActiveSheet.Range("C35:C40")
            .XValues = ActiveSheet.Range("B35:B40")
            .ApplyDataLabels
            For Each objDLabel In .DataLabels
                objDLabel.Text = "=" & rngtext.Cells(lngItem, 1).Address(, , xlR1C1)
            Next
        End With
    End With
End Sub

[ehamike]

Andy
thanks for your help. I got the chart to do what I want and now I have a new problem... actually 2
First, the labels that I'm adding to the right side of the chart are faded. Is there a way to get them to be the same as the rest of the labels. I tried bold but that didn't fix it.
2nd, we use these charts for updates to sr management so they are copied and pasted into word docs. When I copy the chart into a word doc, the axis titles get printed on top of the axis labels
I've attached the pdf from the word doc.
thanks again

[Andy Pope]

The faded text is caused by the true axis labels being in white font rather than set to None.

The horizontal axis line can be removed by setting the line to none.