Sparklines with Rolling 12 Months

[Zee996]

Hi, I am building a dashboard. So tab 1 called "Data" with cell T2 has a list with a dropdown with the month of the year. I am looking for a way for sparkling to show the data of rolling 12 months corresponds to the data selected in the drop-down.

For example, if we select Feb 22, the sparkline should capture Feb 22 to Feb 23, if we choose Mar 23, the Sparkline should capture Mar 22 to Mar 23, etc.

I have the formula that captures the 12 months of data with the help of name range I can create dynamic sparklines.
Formula =OFFSET(Sheet1!$C$1,COUNT(Sheet1!$C:$C),0,-12,1)

But I am stuck on how to inbuild these drop-downs in the formula.

Is this possible to create?

 

[montecarlo2012]

First

Here is a flowchart that illustrates the logic of the VBA code for creating a dynamic sparkline based on a selected month:

START

Define selectedMonth as range T2
Define dataRange as range C2:C100

Find the last row of data in dataRange
Calculate start and end dates for sparkline based on selectedMonth
Create sparkline range using OFFSET function and SPARKLINE function
Update formula of sparkline range to include data range and start/end dates

If selectedMonth is changed, then call UpdateSparkline subroutine

END

now I rather to create a dynamic sparkline using VBA, you can use the following code:

Sub UpdateSparkline()
    Dim selectedMonth As Range
    Dim dataRange As Range
    Dim sparklineRange As Range
    
    Set selectedMonth = Range("T2")
    Set dataRange = Range("C2:C100") 'Assuming your data is in column C
    
    'Find the last row of data
    Dim lastRow As Long
    lastRow = dataRange.Cells(dataRange.Cells.Count).End(xlUp).Row
    
    'Calculate the start and end dates for the sparkline
    Dim startDate As Date
    Dim endDate As Date
    endDate = selectedMonth.Value
    startDate = DateAdd("m", -11, endDate)
    
    'Create the sparkline range using the OFFSET function
    Set sparklineRange = Range("D2") 'Assuming your sparkline starts in column D
    sparklineRange.Formula = "=SPARKLINE(OFFSET(" & dataRange.Address & ", MATCH(" & startDate & "," & dataRange.Address & ",0)-1, 0, 12, 1))"
End Sub

This code first defines the selected month and data range, and then calculates the start and end dates for the sparkline based on the selected month. It then creates the sparkline range using the OFFSET function and the SPARKLINE function. Finally, it updates the formula of the sparkline range to include the correct data range and start/end dates.

You can then call this code from the worksheet using the Worksheet_Change event, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("T2")) Is Nothing Then
        UpdateSparkline
    End If
End Sub

This code checks if the value of cell T2 has changed, and if so, calls the UpdateSparkline subroutine. This ensures that the sparkline is updated automatically whenever the user selects a different month from the dropdown list.

 

[montecarlo2012]

I hope you find help full and see how important is to explained the logic before the code.

 

[Zee996]

Thank you so much for this. It is really helpful and thank you for explaining the logic behind the code.

Thank you so much again

 

[awoohaw]

i tried to build it with a function built into the sparklines dialog, but I could not figure it out:
but try this:

Book1
	A	B	C	D	E	F
1	Date	Value		Choose Date:
2	2022-01-01	1		2022-06-01
3	2022-02-01	1
4	2022-03-01	1				Sparklines Data
5	2022-04-01	1				2
6	2022-05-01	2				2
7	2022-06-01	2				2
8	2022-07-01	2				3
9	2022-08-01	2				3
10	2022-09-01	3				3
11	2022-10-01	3				3
12	2022-11-01	3				4
13	2022-12-01	3				4
14	2023-01-01	4				4
15	2023-02-01	4				4
16	2023-03-01	4				5
17	2023-04-01	4
18	2023-05-01	5
19	2023-06-01	5
20	2023-07-01	5
21	2023-08-01	5
22	2023-09-01	6
23	2023-10-01	6
24	2023-11-01	6
25	2023-12-01	6
26	2024-01-01	7
Zee996

Cell Formulas
Range	Formula
D2	D2	=DATE(2022,6,1)
F5:F16	F5	=INDEX($B$2:$B$26,MATCH($D$2,$A$2:$A$26,0),1):INDEX($B$2:$B$26,MATCH(EDATE($D$2,11),$A$2:$A$26,0),1)
Dynamic array formulas.

 

[awoohaw]

i tried to build it with a function built into the sparklines dialog, but I could not figure it out:
but try this:

Book1
	A	B	C	D	E	F
1	Date	Value		Choose Date:
2	2022-01-01	1		2022-06-01
3	2022-02-01	1
4	2022-03-01	1				Sparklines Data
5	2022-04-01	1				2
6	2022-05-01	2				2
7	2022-06-01	2				2
8	2022-07-01	2				3
9	2022-08-01	2				3
10	2022-09-01	3				3
11	2022-10-01	3				3
12	2022-11-01	3				4
13	2022-12-01	3				4
14	2023-01-01	4				4
15	2023-02-01	4				4
16	2023-03-01	4				5
17	2023-04-01	4
18	2023-05-01	5
19	2023-06-01	5
20	2023-07-01	5
21	2023-08-01	5
22	2023-09-01	6
23	2023-10-01	6
24	2023-11-01	6
25	2023-12-01	6
26	2024-01-01	7
Zee996

Cell Formulas
Range	Formula
D2	D2	=DATE(2022,6,1)
F5:F16	F5	=INDEX($B$2:$B$26,MATCH($D$2,$A$2:$A$26,0),1):INDEX($B$2:$B$26,MATCH(EDATE($D$2,11),$A$2:$A$26,0),1)
Dynamic array formulas.

shoot, the sparklines did not appear.. I'll post an image: