Horizontal Filters

[tinferns]

Hello Team,
Firstly thanks a million for all your help in the past.

I need your help on the below:

I prefer formula(s), but I guess this will work best with a VBA. In the below table, I want to apply a horizontal filter based on Week Number. So If I have selected Week 41, I want to see all 6 departments' dates between 05Oct2020 - 09Oct2020. Rest has to be hidden.

If required can place another table in the file to help VBA choose START DATE & END DATE depending on Year and Week Number. The format I am working is shown below:

VBA FOR HORIZONTAL FILTER.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
1	Week No	Department 1					Department 2					Department 3					Department 4					Department 5					Department 6					Department 1					Department 2					Department 3					Department 4					Department 5					Department 6
2	41	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct	05-Oct	06-Oct	07-Oct	08-Oct	09-Oct	12-Oct	13-Oct	14-Oct	15-Oct	16-Oct	12-Oct	13-Oct	14-Oct	15-Oct	16-Oct	12-Oct	13-Oct	14-Oct	15-Oct	16-Oct	12-Oct	13-Oct	14-Oct	15-Oct	16-Oct	12-Oct	13-Oct	14-Oct	15-Oct	16-Oct	12-Oct	13-Oct	14-Oct	15-Oct	16-Oct
3	Number of Emplooyes
4	Employees Present
5	Employees Absent
6
7	Total Productivity
8	Expected Productivity
9	Actual Producitivty
10	Difference
11
12	Quality Scores
13	Expected QA Scores
14	Difference
15
16	Client Scores
17	Expected Client Scores
18	Difference
19
Sheet1

Start Date and End Date based on Year and Week Number (I can keep this table hidden somewhere in the sheet)

VBA FOR HORIZONTAL FILTER.xlsx
	A	B	C	D
21	Year	Week No	Start Date	End Date
22	2020	41	5-Oct-20	9-Oct-20
23	2020	42	12-Oct-20	16-Oct-20
24	2020	43	19-Oct-20	23-Oct-20
25	2020	44	26-Oct-20	30-Oct-20
26	2020	45	2-Nov-20	6-Nov-20
27	2020	46	9-Nov-20	13-Nov-20
28	2020	47	16-Nov-20	20-Nov-20
29	2020	48	23-Nov-20	27-Nov-20
30	2020	49	30-Nov-20	4-Dec-20
31	2020	50	7-Dec-20	11-Dec-20
32	2020	51	14-Dec-20	18-Dec-20
33	2020	52	21-Dec-20	25-Dec-20
34	2020	53	28-Dec-20	1-Jan-21
35	2021	1	4-Jan-21	8-Jan-21
36	2021	2	11-Jan-21	15-Jan-21
37	2021	3	18-Jan-21	22-Jan-21
38	2021	4	25-Jan-21	29-Jan-21
39	2021	5	1-Feb-21	5-Feb-21
40	2021	6	8-Feb-21	12-Feb-21
41	2021	7	15-Feb-21	19-Feb-21
42	2021	8	22-Feb-21	26-Feb-21
43	2021	9	1-Mar-21	5-Mar-21
44	2021	10	8-Mar-21	12-Mar-21
45	2021	11	15-Mar-21	19-Mar-21
46	2021	12	22-Mar-21	26-Mar-21
47	2021	13	29-Mar-21	2-Apr-21
48	2021	14	5-Apr-21	9-Apr-21
49	2021	15	12-Apr-21	16-Apr-21
50	2021	16	19-Apr-21	23-Apr-21
51	2021	17	26-Apr-21	30-Apr-21
52	2021	18	3-May-21	7-May-21
53	2021	19	10-May-21	14-May-21
54	2021	20	17-May-21	21-May-21
55	2021	21	24-May-21	28-May-21
56	2021	22	31-May-21	4-Jun-21
57	2021	23	7-Jun-21	11-Jun-21
58	2021	24	14-Jun-21	18-Jun-21
59	2021	25	21-Jun-21	25-Jun-21
60	2021	26	28-Jun-21	2-Jul-21
61	2021	27	5-Jul-21	9-Jul-21
62	2021	28	12-Jul-21	16-Jul-21
63	2021	29	19-Jul-21	23-Jul-21
64	2021	30	26-Jul-21	30-Jul-21
65	2021	31	2-Aug-21	6-Aug-21
66	2021	32	9-Aug-21	13-Aug-21
67	2021	33	16-Aug-21	20-Aug-21
68	2021	34	23-Aug-21	27-Aug-21
69	2021	35	30-Aug-21	3-Sep-21
70	2021	36	6-Sep-21	10-Sep-21
71	2021	37	13-Sep-21	17-Sep-21
72	2021	38	20-Sep-21	24-Sep-21
73	2021	39	27-Sep-21	1-Oct-21
74	2021	40	4-Oct-21	8-Oct-21
75	2021	41	11-Oct-21	15-Oct-21
76	2021	42	18-Oct-21	22-Oct-21
77	2021	43	25-Oct-21	29-Oct-21
78	2021	44	1-Nov-21	5-Nov-21
79	2021	45	8-Nov-21	12-Nov-21
80	2021	46	15-Nov-21	19-Nov-21
81	2021	47	22-Nov-21	26-Nov-21
82	2021	48	29-Nov-21	3-Dec-21
83	2021	49	6-Dec-21	10-Dec-21
84	2021	50	13-Dec-21	17-Dec-21
85	2021	51	20-Dec-21	24-Dec-21
86	2021	52	27-Dec-21	31-Dec-21
87	2022	1	3-Jan-22	7-Jan-22
88	2022	2	10-Jan-22	14-Jan-22
Sheet1

Cell Formulas
Range	Formula
C22:C88	C22	=DATE(A22,1,-2)-WEEKDAY(DATE(A22,1,3))+B22*7
D22:D88	D22	=DATE(A22, 1, -2) - WEEKDAY(DATE(A22, 1, 3)) + B22 * 7 + 4

 

[tinferns]

Hi Jason.. Thanks for all your efforts.. but I am sorry to say that it is still not working. As suggested by you I closed all Excel files and opened them, yet it does not seem to work. Screenshots should help you find my mistakes.

Thanks..

 

[jasonb75]

close excel and reopen it

Close excel, not close the files. If you close the files and keep the program open then it doesn't reset.

It works fine for me with the sample from post 1.

 

[tinferns]

Thanks Jason.. it worked.. Thanks a million.. Cheers....