Use List Drop Down to sort date range in spreadsheet

[buyers]

After spending hours trying to figure this out, I'm hoping you all can help. I'd like to use a drop down list with they year (2014/2015/2016/2017) to filter my spreadsheet by a date range of say 2/1/15-1/1/16=2015, 2/1/16-1/1/17=2017, etc.

		2015
1/1/15	xyc	abc	ed	b
12/1/16	dkdjf	akljf	fakldj	falkdj
2/1/17	fdaklj	fjadjk	akdjf;	fdalkj

<tbody>
</tbody>

Would love anyone's help, thank you!!!

 

[Steve_R]

Neither did my earlier message in which I suggested the new WB you sent me indicates you may not have followed tweaks required
1) Turn off any autofilter
2) Resize your table to include column B. In other words include TRUE/FALSE calculations in the datatable (but still hide after)
3) Add 'All' to your data validation list (so you can select all years)

Step 2 is critical.

I will have a look at the most recent sample book sometime later in the day.

 

[Steve_R]

You did seem to miss the table resize.

The 2nd sheet needed to be updated to Table4 and have other minor tweaks made. Sorry if instructions were not clear enough.


Fingers crossed this works for you:
https://onedrive.live.com/redir?res...132&authkey=!AM9NCBQpd-7WuwA&ithint=file,xlsm

 

[buyers]

Works perfect! The only little hitch is that on the sales tracker sheet, for 2014 and 2017 where there is no entries, it shows all. Is there a way to change this to show none? It works fine in the BIF worksheet. Thank you.

 

[Steve_R]

It looks to me like something something you may do once per year. If I was doing it, I'd use a dynamic named range to populate the drop-down list. Dynamic named ranges can be very useful things to use. I've attached a sample WB that tells you how to set it up. Hopefully, your formula-writing experience will make it easy to follow because I did put it together in a hurry.

Quick test first.
Open the drop-down list at F1 in Sheet1 of the attached (which contains set-up instructions) and see what's there. Now add 2017 at A5 and try again.

the process in summary,
1) Populate cells with values
2) Create a dynamic named range that references the first cell in the range
3) Create your form contol
4) Specify the named range as the input range.
5) Link it to a cell (not the cell you use for the marcro to find year)
6) Create a formula in the cell you use for the macro year.

You could set your dynamic named range on a new hidden (or non-hidden) sheet, create more than one - whatever you need.

https://onedrive.live.com/redir?res...135&authkey=!AEvxTCDQl1cDDzk&ithint=file,xlsx

 

[buyers]

Thank you

 

[buyers]

Hi Steve, hope you are well, was hoping you might help me out with something that seems simple that I can't figure out. I have the data validation list (2014,2015,2016,2017, All) on two worsheets that I would like to mirror each other. Said differently, if I select 2015 on worksheet A, I want it to change to 2015 on Worksheet B and vice versa. Any advice would be awesome. Thank you.

 

[Steve_R]

Do you mean a formula in one sheet that is updated when a cell in another sheet changes?

In any sheet other than Sheet2 the following will show the value in Sheet2 cell A1

=Sheet2!A1

 

[buyers]

Two drop down lists on seperate sheets with the same options(2014,2015,2016,2017,all). I'd like them to mirror each other, but still function as drop down lists. I realize I could simply have one drop down list and do as you state above, but not sure how to do it with both being drop down lists that change with each other.

 

[Steve_R]

It just takes a little code in the Worksheet change event.

Right-click a sheet on which you select a value in a drop list.
click View code
paste


Hopefully I have enough comments in the code below for you to read and understand.
Put code in each sheet that contains a drop list (suitably adjusted) and they will synchronise.


NOTE: code comments and some code changes made after pasting. I think code remains correct but I am human.

Private Sub Worksheet_Change(ByVal Target As Range)

'we need to ensure this only fires when the cell linked to the drop list changes.
'this is the Target mentioned in '(ByVal Target As Range)'
' let's call it iSect
'change B3 (below) to the relevant cell address the drop list on 'this' sheet changes
iSect = "B3" 

    If Intersect(Target, Range(iSect)) Is Nothing Then Exit Sub 'so changes to other cells won't trigger what follows 
  
  Application.EnableEvents = False ' turn off event driven code the following does not trigger an endless loop 
    
    ' In the following modify Sheetx to the name of the sheet containing the cell you wish to synchronise with your drop list in 'this' sheet
    ' Also in the following change "A1' to the cell address on Sheetx that needs to change. 
Sheets("Sheetx").Range("A1").value = target.value

        Application.EnableEvents = True ' switch events monitoring back on so the next change will activate the code
End Sub

Minor note of caution. You can update a linked cell to by-pass cell validation if drop lists use different lists of values.


If it seems to not be working run the following from a standard code module and test again before asking a question.

Sub EvntsOn()
Application.EnableEvents = True
End Sub

BTW new questions not directly related to the initial question should be started is a separate thread.

 

[buyers]

Works perfect, thanks again. I will start a new thread for future questions. Appreciate your help.