Charting Dynamic Named Range but intersect range with blanks or zeros

rodp

Board Regular
Joined
Mar 13, 2013
Messages
66
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)
ABCDEF
1Series1232345321243

<tbody>
</tbody>


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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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 2012On 2012Off 2013On 2013
Wk11%98%2%0%
Wk27%92%38%62%
Wk38%91%52%48%
Wk40%100%47%52%
Wk50%99%3%97%...

<tbody>
</tbody>

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")

<tbody>
</tbody>


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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top