?
Solved

Dynamic Ranges and Sparklines

Posted on 2014-08-26
8
Medium Priority
?
1,511 Views
Last Modified: 2014-08-27
I would like to create normal sparklines for a data matrix (horizontal lines next to a table of horizontal data in series).
The width of the table grows and shrinks depending on the state of the sheet.
I want the lines to adjust accordingly.
I tried creating a name that references the 2D matrix using OFFSET, with I'll call "rngMatrix" here.
If it set the sparklines data range to "rngMatrix" - voila!  They update.
BUT....
The moment I hit enter, rngMatrix gets converted to an absolute reference - so the dynamic-ness is gone.
Can't figure out why.
SparkLines.xlsx
0
Comment
Question by:shacho
  • 4
  • 4
8 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40286853
Rory Archibald solved a similar problem to yours in this EE question.  I've adapted his solution for you.

Ideally, the sparklines should be able to use a dynamic range, but as you noted, if one has only one named range listed, it reverts to the absolute cell range.  To get around it, you create named ranges for each of your sparklines and then list them all in the Data Range.

So, I created three named ranges:
rngMatrix7: =OFFSET(Sheet1!$H$7,0,0,1,COUNT(Sheet1!$H$7:$Z$7))
rngMatrix8: =OFFSET(Sheet1!$H$8,0,0,1,COUNT(Sheet1!$H$8:$Z$8))
rngMatrix9: =OFFSET(Sheet1!$H$9,0,0,1,COUNT(Sheet1!$H$9:$Z$9))

Then I added them all to the Data Range:
sparkline dynamic ranges
This seems to preserve the dynamic ranging you're looking for.  Modified workbook attached.

Regards,
-Glenn
EE-SparkLines.xlsx
0
 

Author Comment

by:shacho
ID: 40286942
Ugh - I had a feeling that was going to be the answer... (There are >150 rows).
But hey, whatever works, eh?
Thanks for the solution!

Cheers,

Mike
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40286964
Don't give up!  I've written a couple of macros that will automatically create the necessary number of dynamic ranges AND assign them to the sparkline group.
Option Explicit
Sub Create_Dynamic_Ranges()
    Dim x As Integer
    For x = 7 To 27 'or any upper limit you need
        ActiveWorkbook.Names.Add Name:="rngMatrix" & x, RefersToR1C1:= _
            "=OFFSET(Sheet1!R" & x & "C8,0,0,1,COUNT(Sheet1!R" & x & "C8:R" & x & "C26))"
        ActiveWorkbook.Names("rngMatrix" & x).Comment = ""
    Next x
End Sub

Sub Expand_Sparkline()
    Dim x As Integer
    Dim strRange As String
    Dim strSource As String
    
    For x = 7 To 27
        strRange = strRange & "$G$" & x & ","
        strSource = strSource & "rngMatrix" & x & ","
    Next x
    strRange = Left(strRange, Len(strRange) - 1)
    strSource = Left(strSource, Len(strSource) - 1)
    
    Range("G7").Select
    ActiveCell.SparklineGroups.Item(1).Modify Location:=Range(strRange), _
        SourceData:=strSource
End Sub

Open in new window


Modified macro-enabled workbook attached.  I've only created the first twenty (rows 7-26).

-Glenn
EE-SparkLines.xlsm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:shacho
ID: 40287164
Nifty.  I took a slightly different approach, but thanks for the suggestion!!

Sub MakeSparklineRefs()
    Dim CTR As Integer
    Dim NameName As String, NameRefersTo As String
    Dim SparkLineString As String
    With Me.Range("rngMatrix").Rows
        For CTR = 1 To .Count
            NameName = "rngMatrix_" & Format(CTR, "000")
            NameRefersTo = "=INDEX(rngMatrix," & CTR & ",)"
            Me.Names.Add NameName, NameRefersTo
            SparkLineString = SparkLineString & ", " & NameName
        Next CTR
    End With
    Debug.Print Mid(SparkLineString, 3)
End Sub

Open in new window

0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40287178
Very cool; nice and compact code there.
0
 

Author Comment

by:shacho
ID: 40287317
Cheers.  The references are working perfectly.  I feel like a complete geek, and lovin' it.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40288081
The fact that you named your dynamic range with the "rng" prefix was telling. :-)  

I'm saving this thread.

-Glenn
0
 

Author Comment

by:shacho
ID: 40289269
Thanks for the compliment!  If you end up using that code, be forewarned that the output to the immediate window will wrap if it's long enough.  If that happens, a line break will sneak into the clipboard when you copy it.  Make sure you paste it into Word (or whatever), delete the line break, recopy and paste into the Sparklines dialog box.  I discovered this the hard way...

Cheers,

Mike
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question