Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to construct a data label in an excel chart using a formula building the string from two cells with a line break between them.

This would work in a cell formula:

=A1 & CHAR(10) & B1

and this would work in VBA

=A1 & Chr(10) & B1

as in this post: http://www.ozgrid.com/forum/showthread.php?t=40644 but neither of these approaches works inside a data label. Any ideas?

share|improve this question

migrated from stackoverflow.com Nov 12 '12 at 2:03

This question came from our site for professional and enthusiast programmers.

2  
In order for this to work in cells Word Wrap needs to be enabled. Is this an option in the data label? –  Brad Nov 9 '12 at 16:00
    
No, but if I reference a cell with a line break then it seems to work –  Dan Nov 12 '12 at 6:34
add comment

3 Answers

In VBA you can use this:

Dim str as String

str = Range("A").Value & vbCrlf & Range("B").Value

vbCrlf will give you line break...

share|improve this answer
1  
=A1 & CHAR(10) & B1 works too, if you do as Brad says and set the cell to use word wrap. You cannot set this up inside of the dataseries definition. It has to be referenced to a cell or setup with vba. –  Jook Nov 9 '12 at 16:11
    
=A1 & Chr(10) & B1 works perfectly in VBA as well as I mentioned in my question. Solving this in VBA is not the problem but thanks anyway. Also vbNewLine works which is probably the most readable choice if you use VBA. –  Dan Nov 12 '12 at 6:41
add comment

This worked for me in VBA:

Sheet1.ChartObjects(1).Chart.SeriesCollection(1).Name = "a" & vbCr & "b"

And as said, this works with a formula:

=DATASERIES(Sheet1!$E$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)

E1 = "a" & CHAR(10) & "b"

E1 has to have word wrap set.

DATASERIES is what you see in the formula edit section, when you have selected your series.

share|improve this answer
    
The DATASERIES part is unnecessary, all I needed was to put the line break in a cell and then make the data label reference that cell. So =A1&CHAR(10)&B1 in a word wrapped cell (say C1) and then in the data label just =C1 works. I'm not sure if this is what you were saying in your answer but I'm going to mark it as correct, perhaps you can edit it to reflect this comment. –  Dan Nov 12 '12 at 6:38
    
OK I see the advantage of setting it with that DATASERIES formula but it gives me an error. I'm using excel 2012. If I click on a series the formula I get is this: =SERIES("Floating Rate Notes",NSS!$I$35:$I$85,NSS!$L$35:$L$85,5) I have tried adding the data label range as a parameter in a few places but nothing works, and also I tried changing SERIES to DATASERIES but also no good. Any ideas? –  Dan Nov 12 '12 at 7:41
    
My Example was on excel2007, and yes, it ment, what you described in your comment. I just used the DATASERIES formula instead of the corresponding dialog. Based on what you wrote, I would have changed it to e.g. =SERIES(NSS!$I$34,NSS!$I$35:$I$85,NSS!$L$35:$L$85,5) but I cannot test that. –  Jook Nov 12 '12 at 12:05
    
Wait but then where do you put the series name which is the first parameter in my SERIES() function? And also if you are just referencing one single cell, then you only get one datalabel no? I need all of points to have labels referencing different cells, so replace NSS!$I$34 with a range. But looking at your formula, it seems like you are trying to get the linebreak in the series name, not a datalable. I have solved this with VBA, but if there is a way to not I'm keen to hear it. –  Dan Nov 12 '12 at 13:06
    
Ah, ok, yeah, then here was a misunderstanding - I would not know of any way to individually assign values to data-lables without VBA. Somehow I was under the strong impression you ment series-label. –  Jook Nov 12 '12 at 13:13
show 1 more comment
up vote 0 down vote accepted

So this is how I got it working.

Step 1: create a new column with wordwrap enabled with the formula =A1 & CHAR(10) & B1 in cell C1 and drag down as appropriate

Step 2: Run the following VBA code to set each data label to point to the correct cell

Sub SetDataLabels()

    Dim ChartName As String
    ChartName = "My Chart's Name"

    With ActiveSheet.ChartObjects(ChartName).Chart

        Dim Series As Integer
        Series = 4

        With .SeriesCollection(Series)

            Dim currentPoint As Integer

            For currentPoint = 1 To .Points.Count

                .Points(currentPoint).DataLabel.Text = Range("C" & currentPoint).Text

            Next currentPoint
        End With

    End With

End Sub
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.