Select last 12 items in a drop down list

[nguerra]

Hoping someone can help with this topic. I have a drop down that has 24 items that are always rolling or being updated. I'm looking for a way to filter on the last 12.

 

[JustynaMK]

Assuming that your items are in Sheet1, column A (starting in cell A1), you can create a Named Range using the following formula:

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-12,0,12,1)

This will always "select" the last 12 items. You can then reference this Named Range in your drop-down list.

Let me know if it works for you.

 

[Yongle]

Which type of dropdown list is this ?
- data validation list
- worksheet form control LISTBOX \ COMBOBOX
- worksheet active-x control LISTBOX \ COMBOBOX
- userform LISTBOX \ COMBOBOX

What is the source of the 24 values ?

 

[nguerra]

Not sure how to incorporate that. The recorded macro looks like this:

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Data].[Month].[Month]" _
).VisibleItemsList = Array("[Data].[Month].&[yr2018mth08]", _
"[Data].[Month].&[yr2018mth09]", "[Data].[Month].&[yr2018mth10]", _
"[Data].[Month].&[yr2018mth11]", "[Data].[Month].&[yr2018mth12]", _
"[Data].[Month].&[yr2019mth01]", "[Data].[Month].&[yr2019mth02]", _
"[Data].[Month].&[yr2019mth03]", "[Data].[Month].&[yr2019mth04]", _
"[Data].[Month].&[yr2019mth05]", "[Data].[Month].&[yr2019mth06]", _
"[Data].[Month].&[yr2019mth07]")

Only issue is, this will continually roll or update.

 

[nguerra]

Anyone able to take a stab at making the below recorded macro a VBA?

Not sure how to incorporate that. The recorded macro looks like this:

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Data].[Month].[Month]" _
).VisibleItemsList = Array("[Data].[Month].&[yr2018mth08]", _
"[Data].[Month].&[yr2018mth09]", "[Data].[Month].&[yr2018mth10]", _
"[Data].[Month].&[yr2018mth11]", "[Data].[Month].&[yr2018mth12]", _
"[Data].[Month].&[yr2019mth01]", "[Data].[Month].&[yr2019mth02]", _
"[Data].[Month].&[yr2019mth03]", "[Data].[Month].&[yr2019mth04]", _
"[Data].[Month].&[yr2019mth05]", "[Data].[Month].&[yr2019mth06]", _
"[Data].[Month].&[yr2019mth07]")

Only issue is, this will continually roll or update.

 

[Yongle]

You would have been more likely to receive a more relevant response to your question if your thread title and post#1 had made it clear that this is a VBA and pivot table dropdown issue

The code below is untested with a pivot table. The generated string appears to be correct - however that does not guarantee that line for the dropdown will function correctly

The string generated by VBA with combination year 2019 and month 7 is this
"[Data].[Month].&[yr2018mth08]","[Data].[Month].&[yr2018mth09]","[Data].[Month].&[yr2018mth10]","[Data].[Month].&[yr2018mth11]","[Data].[Month].&[yr2018mth12]","[Data].[Month].&[yr2019mth01]","[Data].[Month].&[yr2019mth02]","[Data].[Month].&[yr2019mth03]","[Data].[Month].&[yr2019mth04]","[Data].[Month].&[yr2019mth05]","[Data].[Month].&[yr2019mth06]","[Data].[Month].&[yr2019mth07]"
( matches what you provided in post#4 )

Add worksheet named "Strings" (illustrated below)
- VBA amends values in A2 and B2 (based on user input)
- everything else in the sheet is formula driven
- VBA then concatenates the strings found in D2:D13

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Month[/td][td]year[/td][td][/td][td][Data].[Month].[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

7​

[/td][td]

2019​

[/td][td=bgcolor:#DDEBF7]07[/td][td=bgcolor:#FFF2CC][Data].[Month].&[yr2019mth07][/td][td=bgcolor:#FCE4D6] formula in A3 copied down[/td][td] =MONTH(EDATE(DATE(B2,A2,1),-1))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#FCE4D6]

6​

[/td][td=bgcolor:#E2EFDA]

2019​

[/td][td]06[/td][td][Data].[Month].&[yr2019mth06][/td][td=bgcolor:#E2EFDA] formula in B3 copied down[/td][td] =YEAR(EDATE(DATE(B2,A2,1),-1))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

5​

[/td][td]

2019​

[/td][td]05[/td][td][Data].[Month].&[yr2019mth05][/td][td=bgcolor:#DDEBF7] formula in C2 copied down[/td][td] =TEXT(A2,"00")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

4​

[/td][td]

2019​

[/td][td]04[/td][td][Data].[Month].&[yr2019mth04][/td][td=bgcolor:#FFF2CC] formula in D2 copied down[/td][td] =$D$1&"&[yr" & B2 & "mth" & C2 & "]"[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

3​

[/td][td]

2019​

[/td][td]03[/td][td][Data].[Month].&[yr2019mth03][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

2​

[/td][td]

2019​

[/td][td]02[/td][td][Data].[Month].&[yr2019mth02][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

1​

[/td][td]

2019​

[/td][td]01[/td][td][Data].[Month].&[yr2019mth01][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

12​

[/td][td]

2018​

[/td][td]12[/td][td][Data].[Month].&[yr2018mth12][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

11​

[/td][td]

2018​

[/td][td]11[/td][td][Data].[Month].&[yr2018mth11][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

10​

[/td][td]

2018​

[/td][td]10[/td][td][Data].[Month].&[yr2018mth10][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

9​

[/td][td]

2018​

[/td][td]09[/td][td][Data].[Month].&[yr2018mth09][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

8​

[/td][td]

2018​

[/td][td]08[/td][td][Data].[Month].&[yr2018mth08][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Strings[/td][/tr][/table]

VBA

Sub nguerra()
    Dim Q As String:     Q = Chr(34)
    Dim Mth As Long, Yr As Long, r As Long, ws As Worksheet, [COLOR=#ff0000]VSL[/COLOR] As String
    Set ws = Sheets("Strings")
[I][COLOR=#006400]'ask user and write to sheet[/COLOR][/I]
    Yr = InputBox("Year ?", "Which Year", 2019)
    Mth = InputBox("Month", "Which Month", 7)
    ws.Range("A2:B2") = Array(Mth, Yr)
[COLOR=#006400][I]'create array string[/I][/COLOR]
    VSL = Q & ws.Cells(13, 4) & Q
    For r = 12 To 2 Step -1
        [COLOR=#ff0000]VSL[/COLOR] = VSL & "," & Q & ws.Cells(r, 4) & Q
    Next r
[COLOR=#006400][I]'your code[/I][/COLOR]
    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Data].[Month].[Month]").VisibleItemsList = Array([COLOR=#ff0000]VSL[/COLOR])
End Sub

 

[nguerra]

Thank you Yongle, I think you are correct in that the Title needed more detail. I thank you for your help below, not quite what I'm looking for where intervention is needed by the user. I will see if I can incorporate it somehow or re-ask the question. Thank you everyone!

 

[Yongle]

not quite what I'm looking for where intervention is needed by the user

So what are you looking for ?

If you want this to be automated then you need to tell us where the values for month and year are located so that VBA can use them
- probably a very minor mod required to previous code

 

[nguerra]

Well I receive a pivot table once a month. One of the filters has the options of:
yr17mth08,yr17mth09,yr17mth10,yr17mth11,yrmth12,
yr18mth01,yr18mth02,yr18mth03,yr18mth04,yr18mth05,yr18mth06,yt18mth07,yr17mth08,yr18mth09,yr18mth10,yr18mth11,yr18mth12,
yr19mth01,yr19mth02,yr19mth03,yr19mth04,yr19mth05,yr19mth06,yt19mth07.
however, every month the oldest field drops off and a new field is added. It's basically a rolling 24 months of data. The report generated monthly is only looking for the last 12 months. It's important to know that the values in the pivot table are not formatted as dates. I believe they are just strings.
[TABLE="width: 97"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

However, Every month those options change. The oldest one drops off and a new one is added to the bottom.

 

[Yongle]

I expected your sheet to contain the values of latest month and year - but your narrative suggests that's not so

I am guessing that you need VBA
- to interrogate ActiveSheet.PivotTables("PivotTable1").PivotFields("[Data].[Month].[Month]").VisibleItemsList
- and determine the last 12 items
- and filter based on those

That is something I cannot help you with. I suggest you start a new thread with a helpful title, together with a concise but clear and full explanation of your requirement.
Good luck