how to automatically apply filters within a date range

[bigdan]

I have to keep changing my filters with various date ranges and it's a real pain having to keep checking 20 boxes out of a thousand every time I want to change the range.

Is there a way I can specify the dates I want to filter in, say, A1 and A2 (start and end range) and I see the new filtered list?

 

[Toadstool]

Assuming your date is in column E, you could add a new column that says

=IF(AND(E4>=$A$1, E4<= $A$2),1,0)

then just reapply the filter on that new column if you change the dates?

	A	B	C	D	E	F
1	1/1/2019
2	1/31/2019
3	Fliter Check	Z 2	Z 3	Z 4	Z 5	Z 6
4	1	FF	2	FF	1/11/2019	FF
5	1	D	33	2	1/30/2019	D
6	0	CC	44	2	2/2/2019	D
7	1	vv	77	33	1/15/2019	44
8	0	CC	44	2	3/2/2019	D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A4 =IF(AND(E4>=$A$1, E4<= $A$2),1,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[bigdan]

This idea is sooo awesome!!!! Thank you so much

 

[Toadstool]

You're welcome!

Glad to help.

 

[bigdan]

I wonder if I can use this method for my more complicated filters?

I used the if statement above for dates, and I then modified it for both Date and Status and Weight. So for example if I want to see the data between Feb 15 and Feb 25 with a status of Shipped and weight of Bulk then I input that into the first two rows, and I see all the relevant data displayed below. So far that works great.

I'd like to add another wrinkle please. If in C2 (where it currently says Shipped) I delete that figure, at the moment that would give me an error with the filter. Instead, I'd like it to then display all that statuses (but still meeting the other criteria of start and end dates and the weight).

Is that possible?

Start Date	End Date	Status	Weight
2/15	2/25	Shipped	Bulk
Dates	Status	Weight
1/01	Shipped	Bulk
2/02	Cancelled	Parcel
3/31	Completed	Bulk
4/05	Shipped	Parcel

<tbody>
</tbody>

 

[Toadstool]

Hi bigdan,

Yes, we just need to say:
If the rowdate is greater than or equal to the Start Date
AND
the row date is less than or equal to the End Date
AND
the row Status is empty OR equal to Status check
AND
the row Weight is empty OR equal to Weight check
THEN filter = 1
ELSE filter = 0

=IF(AND(A4 >= $A$2,A4 <= $B$2,OR($C$2="",B4=$C$2),OR($D$2="",C4=$D$2)),1,0)

	A	B	C	D
1	Start Date	End Date	Status	Weight
2	2/15/2019	2/25/2019		Parcel
3	Dates	Status	Weight	Fliter Check
4	1-Jan	Shipped	Bulk	0
5	15-Feb	Cancelled	Parcel	1
6	25-Feb	Completed	Bulk	0
7	5-Apr	Shipped	Parcel	0

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D4 to D last data row =IF(AND(A4>=$A$2,A4<=$B$2,OR($C$2="",B4=$C$2),OR($D$2="",C4=$D$2)),1,0) <tbody> </tbody>

<tbody>
</tbody>

I used LoVs for the Status and Weight check (to avoid typos) with:
C2 Data, Data Validation, List Source Shipped,Cancelled,Completed
D2 Data, Data Validation, List Source Bulk,Parcel

Of course you just use the Delete key to empty either or both