Filter Data based on two cells

[KelvinBruns]

Hello:
I have a report that is being dumped into excel. From that data, I have a Index Formula that is returning data based on client. When that data returns I need to be able to filter by date. I need to be able to type the date range in two separate cells.

<tbody> </tbody> Client: BLU Termed Date Range From: 9/1/2016 To: 10/9/2016 ID: Name Site Date 11644 Sample BLU 11608 Sample BLU 4/13/16 11609 Sample BLU 9/28/16 11606 Sample BLU 9/13/16 11697 Sample BLU 1/13/16 51689 Sample BLU 9/26/16 <colgroup><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[KelvinBruns]

I need to be able to return the data between the two dates.

 

[CatOnASpreadsheet]

Hi KelvinBruns,

Do you want to visually only show all the data for the records between those dates on your spreadsheet or are you looking to obtain a range to work with inside another formula?

Cheers,
Catalin

 

[KelvinBruns]

Visually I would like to only show the data records that are between the dates.

 

[CatOnASpreadsheet]

Hi KelvinBruns,

I think you're stuck with using Excel's Filters for that. You could of course create a new table which has a test for every value - along the lines of =IF(AND(D2>=$G$2,D2<=$I$2),D2,"") where G2 and I2 hold your input dates - but that is going to be messy, leaving blank rows all over the data.

If you can go the VBA route, I can provide a quick sub to populate the data in a specified range accordingly. Ideally you'd want your original data on one sheet and the inputs and results on a separate sheet. There's also the option of using VBA to apply the filters for you when the inputs change.

Cheers,
Catalin

 

[KelvinBruns]

The VBA route would be fine. I am not super comfortable using VBA and that is why I was trying to get around from using it.

 

[CatOnASpreadsheet]

Hi KelvinBruns,

You can use the following code to achieve the goal. To use it, open the Visual Basic Editor by pressing Alt+F11 on your keyboard. On the top left-hand side you will see the list of sheets in your workbook. Double-click the one your data is in and paste the code in there. Make sure to scan through the code and make the necessary changes - the name of your worksheet for one, and I have made a number of assumptions regarding your data structure and positioning.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rInput As Range
Dim dStart As Date
Dim dEnd As Date
Dim lngLastRow As Long


'The code assumes your start date is in G1, your end date is in I1
'and that the data resides in columns A:D, starting in row 1


Set rInput = Range("G1,I1")
    
If Not Application.Intersect(rInput, Range(Target.Address)) Is Nothing Then


    With ThisWorkbook.Sheets("YourSheetNameHere")
        'replace sheet name as appropriate
    
        dStart = .Range("G1").Value
        dEnd = .Range("I1").Value 'read the start and end dates
        
        lngLastRow = .Range("A1048576").End(xlUp).Row 'read the last row of data
        
        .Range("A1:D" & lngLastRow).AutoFilter 'remove any existing filters
        
        .Range("A1:D" & lngLastRow).AutoFilter Field:=1, Criteria1:= _
            ">=" & dStart, Operator:=xlAnd, Criteria2:="<=" & dEnd
        'add the new filter
        
    End With
       
End If


Set rInput = Nothing


End Sub

I hope this helps.

Cheers,
Catalin