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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

rodp

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

Watch MrExcel Video

Forum statistics

Threads
1,095,158
Messages
5,442,711
Members
405,194
Latest member
Aprivate

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top