Results 1 to 2 of 2

Charting Dynamic Named Range but intersect range with blanks or zeros

This is a discussion on Charting Dynamic Named Range but intersect range with blanks or zeros within the Excel Questions forums, part of the Question Forums category; Hi All, I need to create a dynamic named range to use in a chart from the following data but ...

  1. #1
    Board Regular
    Join Date
    Mar 2013
    Posts
    66

    Cool Charting Dynamic Named Range but intersect range with blanks or zeros

    Hi All,

    I need to create a dynamic named range to use in a chart from the following data but intersect each data point with a blank or zero. I'm unable to move each and every cells along so that you can select the range in the final data as the data comes from an external source.

    example data (on sheet1)
    A B C D E F
    1 Series1 23 234 532 12 43


    resulting chart data for series1.
    ={23,0,234,0,532,0,43}

    I thought maybe using concatenate in the named range array but perhaps this isn't right, for instance

    =concatenate(Sheet1!B1:F1,",",0). This doesn't work anyway as it attempts to put the result value in each cell as a text item instead of keeping it as a value.

    The reason I need this in the form of a dynamic range is so that the chart updates automatically and preferably without a macro / some VB running behind the scenes to update the ranges.

    Hope someone can help.

    Many thanks in advance

    Rodp

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    66

    Cool Re: Charting Dynamic Named Range but intersect range with blanks or zeros

    Hi All,

    Well I've been busy... I had to give up on the native formulas and wrote some VBA coding to do this job. Below is the code which referenced Chip Pearson's array function module. The learning here is that when you bring in a range from a worksheet it is infact a 2 dimensional array rather than single. And so when I was testing this originally, i was using a simple array, array(1,2,3,4,5) in the immediate window. I then wondered why testing out a normal range eg Chart_Data!$B$2:$B$10 didn't work as it kept saying there was nothing in the array or subscript error / type mismatched (I was using ? ArrInputArray(1) which didn't work as you needed to do ArrInputArray(1,1) for example). So once I'd realised this, I just had to convert it to a 1 dimension array.

    You use this formula in a named range and then reference the Named Range in the chart. The reason I developed this was to help in creating a Multi Clustered stacked barchart, equivalent to Clustered and Stacked Column and Bar Charts | Peltier Tech Blog | Excel Charts. My method doesn't require messing about aligning and inserting lines etc in the raw data.

    Again if anyone has any suggestions in improving this do let me know.

    Cheers

    Rodp

    ----------------------------

    Here's an example table and formulas:

    Off 2012 On 2012 Off 2013 On 2013
    Wk1 1% 98% 2% 0%
    Wk2 7% 92% 38% 62%
    Wk3 8% 91% 52% 48%
    Wk4 0% 100% 47% 52%
    Wk5 0% 99% 3% 97%...

    Named Range ...... RefersTo:
    Chart1_off_2012 =createclusterchartarray(Chart_Data!$H$2:$H$11,0,2,0,DESIREDARRAYLENGTH,TRUE,"COLUMN")
    Chart1_on_2012 =createclusterchartarray(Chart_Data!$I$2:$I$11,0,2,0,DESIREDARRAYLENGTH,TRUE,"COLUMN")
    Chart1_off_2013 =createclusterchartarray(Chart_Data!$J$2:$J$11,0,2,1,DESIREDARRAYLENGTH,TRUE,"COLUMN")
    Chart1_on_2013 =createclusterchartarray(Chart_Data!$K$2:$K$11,0,2,1,DESIREDARRAYLENGTH,TRUE,"COLUMN")


    Here's the code:

    Code:
    Function CreateClusterChartArray(RngInput As Range, ValueToUse As Long, NumberToInsertAtEachDataPoint As Integer, OffsetNumber As Integer, DesiredArrayLength As Integer, AddExtraAtStarAndEnd As Boolean, ColumnOrRow As String) As Variant
    
    'References modArraySupport By Chip Pearson - Thanks Chip!
    
    Dim x As Long
    Dim y As Integer
    Dim Result As Boolean
    Dim Str As String
    Dim ArrInputArray()
    
    'A range returns a 2 dimensional range so we need to convert it to 1 for function to work
    If UCase(ColumnOrRow) = "COLUMN" Then
        ReDim ArrInputArray(RngInput.Rows.Count - 1)
        For y = 1 To RngInput.Rows.Count
        
        If IsError(RngInput(y, 1)) = True Then
            ArrInputArray(y - 1) = 0
            Else
            ArrInputArray(y - 1) = RngInput(y, 1)
        End If
        Next y
    
    Else
        
        ReDim ArrInputArray(RngInput.Columns.Count - 1)
        For y = 1 To RngInput.Columns.Count
        
        If IsError(RngInput(1, y)) = True Then
            ArrInputArray(y - 1) = 0
            Else
            ArrInputArray(y - 1) = RngInput(1, y)
        End If
        Next y
    
    End If
    
    
    'Str = ""
    'For x = LBound(ArrInputArray) To UBound(ArrInputArray)
    ' Str = Str & " " & ArrInputArray(x)
    'Next x
    'Debug.Print RngInput.Address & "Before: " & Str
    
    
    For x = UBound(ArrInputArray) To LBound(ArrInputArray) + 1 Step -1
        For y = 1 To NumberToInsertAtEachDataPoint
        Result = InsertElementIntoArray(ArrInputArray, x, ValueToUse)
        Next y
    Next x
    
    
    'add entry at end
    For y = 1 To NumberToInsertAtEachDataPoint
        Result = InsertElementIntoArray(ArrInputArray, UBound(ArrInputArray) + 1, ValueToUse)
    Next y
    
    
    'add offset to beginning
    For y = 1 To OffsetNumber
        Result = InsertElementIntoArray(ArrInputArray, LBound(ArrInputArray), ValueToUse)
    Next y
    
    
    
    
    'add extra entry at start and end if required
    If AddExtraAtStarAndEnd = True Then
        Result = InsertElementIntoArray(ArrInputArray, UBound(ArrInputArray) + 1, ValueToUse)
        Result = InsertElementIntoArray(ArrInputArray, LBound(ArrInputArray), ValueToUse)
    End If
    
    
    'cut off or add extra 0 is array not equial to max length
    
    
    Do While UBound(ArrInputArray) + 1 < DesiredArrayLength
    Result = InsertElementIntoArray(ArrInputArray, UBound(ArrInputArray) + 1, ValueToUse)
    Loop
    
    
    Do While UBound(ArrInputArray) + 1 > DesiredArrayLength
    Result = DeleteArrayElement(ArrInputArray, UBound(ArrInputArray), True)
    Loop
        
    CreateClusterChartArray = ArrInputArray
    
    
    'Str = ""
    'For x = LBound(ArrInputArray) To UBound(ArrInputArray)
    ' Str = Str & " " & ArrInputArray(x)
    'Next x
    'Debug.Print RngInput.Address & "After: " & Str
    
    
    End Function

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com