Creating Dynamic Sparkline that correspond to different columns using named range?

  • hi!


    I have a set of month-wise data from which I need to draw sparklines using native excel sparkline option, in multiple sheets of Excel.


    Requirement:


    If A1= Jan, then sparkline corresponding to data in column J to be drawn in cell B1
    If A1= Feb, then sparkline corresponding to data in column K to be drawn in cell B1
    If A1= Mar, then sparkline corresponding to data in column L to be drawn in cell B1


    Above need to be done WITHOUT creating an additional column of hlookups for the dynamic named range


    Formula used for my dynamic range is

    Code
    =OFFSET(Sheet1!$J$2,,,COUNT(Sheet1!$J$2:$J$50))


    Can this formula be made dynamic (using Indirect or something similar) so that the the named range (press Ctrl+F3 to see named range) can pick values from some other cell and change column references to Col. J, K, L etc. and not just be stuck to J2 and J2:J50


    Sample file attached.


    Note: The thread is a continuation of another thread posted on this forum at: http://www.ozgrid.com/forum/showthread.php?t=183293 That thread is closed since I feel I was asking the wrong question there.


    Regards,


    Naira

  • Re: Creating Dynamic Sparkline that correspond to different columns using named range


    No need for named ranges:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$1" Then Target.Offset(, 1).SparklineGroups.Item(1).Item(1).SourceData = Columns(9 + Application.Match(Target, Array("Jan", "Feb", "Mar"), 0)).SpecialCells(2).Offset(1).SpecialCells(2).Address
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!