Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OFFSET/COUNTA function (MS Excel 2003)

    My question is two fold: On the attachment, I have tried to create a formula in B15 (for Current Acutal) that is equal to the last value entered in the table. However, my attempt has a circular argument

    =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$-1)

    Also, would it be possible to illustrate more than one value on the chart in the schema I have chose. I have used a clustered column with "0" gap and no color fill. I would essentially want to have an overlapping cluster column with "0" gap and no color fill but use a different line color perhaps. Do you have a recommendation on how to attack this another way?

    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    Since you want to place the formula in a cell in column B, you shouldn't use B:B since that will include the cell with the formula. Moreover, the row shift should be the first argument after $B$2. Try this:

    =OFFSET($B$1,COUNTA($B$2:$B$13),0)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    I'm afraid I don't understand your question about the chart. Could you try to explain again?

  4. #4
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    Thank you for the correction.

    What are your thoughts on illustrating a second value in the attached chart like the value in B17?
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    You could make the two series overlap 100% - see attached version. I also specified "inside" as position for the data label of the second series, otherwise it was unclear what it belonged to.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    Hans,
    Thanks for your time. That will works well with multiple values.

    Amy

  7. #7
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    Hans,
    As I am looking at this, is it possible to code the DataLabels above or outside of the PlotArea, but still inside the ChartArea?

    I have attached the example bar chart from the original example.

    The following code from the Mr. Excel folks specifies location and orientation of data labels, but I don't know if I am limited to my first question above:

    Sub DataLabelDemo()
    With Cahrts("Chart1").SeriesCollection("Xdata")
    .HasDataLabels = True
    .ApplyDataLabels Type: xlDataLabelsShowValue
    With .DataLabels
    .HorizontalAlignment = xlCenter
    .VerticalAlignment=xlCenter
    .Position = xlLabelPositionAbove
    .Orientation = xlUpward
    End With
    End With
    End Sub

    I would like to locate and orient all data labels just above the bar graph, right above the end.

    Thanks
    AmyN
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    You can specify the Top and Left properties of a datalabel but the problem is that although you also know the Top and Left properties of the plot area, there is no easy way to determine the coordinates of a data series. This is the best I can come up with:
    <code>
    Sub ShiftDataLabels()
    Dim dblTop As Double
    dblTop = ActiveChart.PlotArea.Top
    ' Shift plot area down to make room for data labels
    ActiveChart.PlotArea.Top = dblTop + 15
    With ActiveChart.SeriesCollection(1).Points(1).DataLabe l
    .Position = xlLabelPositionOutsideEnd
    .Top = dblTop - 15
    End With
    With ActiveChart.SeriesCollection(2).Points(1).DataLabe l
    .Position = xlLabelPositionInsideEnd
    .Top = dblTop - 15
    End With
    End Sub</code>

  9. #9
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    I wasn't able to designate the code below to a specific chart with a worksheet of multiple charts.

    Also, another approach to better identifying the data label would be to give a colored Patterns Area, but I would rather give the value a Shadow Effect. However, the Shadow Effect doesn't seem to be an option.

    Is it possible to change the data label font to a shadow effect with code? How would you do that for the data label of a specific chart and also for a specific range on the worksheet?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    To apply the code from my previous reply to a specific chart, replace ActiveChart with something like
    <code>
    Worksheets("MySheet").ChartObjects(3).Chart
    </code>
    or
    <code>
    Worksheets("MySheet").ChartObjects("Chart3").Chart
    </code>
    To apply a shadow to a datalabel:
    <code>
    With Worksheets("MySheet").ChartObjects("Chart3").Chart .SeriesCollection(1).Points(1).DataLabel
    .Shadow = True
    ' other properties
    ...
    End With</code>

  11. #11
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    Despite my best effort, combining these is not as easy. My bar chart is Chart 8. How would they be integrated so that the DataLabel position code and DataLabel Shadow effect code only applies to the top chart or (Chart 8) in my worksheet.

    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Source: Woody's Lounge (http://www.wopr.com/cgi-bin/w3t/wwwthreads.pl)
    Dim obj As ChartObject
    Dim cht As Chart
    Dim axs As Axis
    Dim dif As Double
    Dim unt As Double
    If Not Intersect(Target, Range("E2:E6")) Is Nothing Then
    Application.EnableEvents = False
    For Each obj In Me.ChartObjects
    Set cht = obj.Chart
    Set axs = cht.Axes(xlValue)
    dif = (Range("E2") - Range("E6")) / 20
    axs.MinimumScale = Range("E6") - dif
    axs.MaximumScale = Range("E2") + dif
    unt = axs.MajorUnit
    axs.MinimumScale = unt * Int(axs.MinimumScale / unt)
    axs.MaximumScale = unt * -Int(-axs.MaximumScale / unt)
    Next obj
    Application.EnableEvents = True
    End If
    End Sub

    Sub ShiftDataLabels()
    Dim dblTop As Double
    dblTop = ActiveChart.PlotArea.Top
    ' Shift plot area down to make room for data labels
    ActiveChart.PlotArea.Top = dblTop + 15
    With ActiveChart.SeriesCollection(1).Points(1).DataLabe l
    .Position = xlLabelPositionOutsideEnd
    .Top = dblTop - 15
    End With
    With ActiveChart.SeriesCollection(2).Points(1).DataLabe l
    .Position = xlLabelPositionInsideEnd
    .Top = dblTop - 15
    End With
    End Sub

    With Worksheets("MySheet").ChartObjects("Chart8").Chart .SeriesCollection(1).Points(1).DataLabel
    .Shadow = True
    End With

  12. #12
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    C'est dommage! I get a Compile Error.

    SeriesCollection is highlighted saying the Sub or Function is not defined.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    Sorry, the first one should have a <code>.</code> before it just like the second one. I'll edit my reply.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    Edited by HansV to correct typo (forgot a . before the first SeriesCollection)

    Try
    <code>
    Sub ShiftDataLabels()
    Dim dblTop As Double
    With Worksheets("MySheet").ChartObjects("Chart 8").Chart
    dblTop = .PlotArea.Top
    ' Shift plot area down to make room for data labels
    .PlotArea.Top = dblTop + 15
    With .SeriesCollection(1).Points(1).DataLabel
    .Position = xlLabelPositionOutsideEnd
    .Top = dblTop - 15
    .Shadow = True
    End With
    With .SeriesCollection(2).Points(1).DataLabel
    .Position = xlLabelPositionInsideEnd
    .Top = dblTop - 15
    .Shadow = True
    End With
    End With
    End Sub
    </code>
    Change the name "MySheet" to the actual name of your worksheet and "Chart 8" to the actual name of your chart.

  15. #15
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OFFSET/COUNTA function (MS Excel 2003)

    I'll understand if my Lounge privileges are revoked after beating this one to death.

    The code definetly executed the desired behavior except for the shadow effect that I am looking for. I would like to do the data label (value) in bold, black with a white shadow or outline or try a data label in white, bold with black shadow or outline. The shadow effect executes against the area that the number value is in vice the number itself. Is it possible to have a more pronounced shadow effect on the number itself or an embossed effect that would make it standout against a colored background. I want to be able to put black font against a navy blue plot area and have the number itself shadowed or embossed in white. I am trying to do the same effect for H2:H4.

    And how do you put the lable on inside of the plot area again.

    Also, as I was playing with this on my home version of Office 2007, I noticed that Excel 2007 actually provides the capability to do the shadow effect with chart data labels. It doesn't do the positioning I am trying to do though. Unfortunately, my company won't be converting for another couple of years, so this 2003 coding is still needed unfortunately.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •