Excel Filtering for a Summary Tab

[ItalianPlatinum]

I have a 5k+ row sheet that contains Invoices, job Ids, Merchants, with Due Dates and Shipment dates among a lot of other unique data. I need a way to quickly and easily identify based off of year when something was shipped. I want to maintain one sheet lets call it data. Then be able to look at another sheet say (2024) or (2025) to reference right there automatically what has been shipped for that year. The sheet 2024 will have all the columns in the data tab that I populated. So essentially a summary filtered by Ship date of 2024. Would anyone be able to help? The user isn't very computer inclined so having them filter or advance filtering themselves is a no can do. I know Excel has FILTER and UNIQUE filter formulas that I think may work but I had issues getting them to work so looking for some help. Thank you in adcance!

Job #	Shipment Date	Due Date	A	B	C	D	X	X	X	X
1	1/1/2023	12/2/2022	AA	12	123	1234	X	X	X	X
2	1/1/2022	12/2/2021	BB	66	55	77	X	X	X	X
3	1/1/2024	12/2/2023	CC	99	88	24	X	X	X	X
4	10/1/2024	9/1/2024	DD	856	874	314	X	X	X	X
5	10/5/2020	9/5/2020	EE	101	202	303	X	X	X	X
6	9/9/2020	8/10/2020	FF	444	555	666	X	X	X	X
7	9/1/2024	8/2/2024	GG	888	999	110	X	X	X	X
8	11/1/2024	10/2/2024	HH	5687	45874	2365	X	X	X	X

 

[JamesCanale]

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Data Sheet with Table												Other tab with output
2													2024
3	Job #	Shipment Date	Due Date	A	B	C	D	X	X2	X3	X4		Job #	Shipment Date	Due Date	A	B	C	D	X	X	X	X
4	1	1/1/2023	12/2/2022	AA	12	123	1234	X	X	X	X		3	1/1/2024	12/2/2023	CC	99	88	24	X	X	X	X
5	2	1/1/2022	12/2/2021	BB	66	55	77	X	X	X	X		4	10/1/2024	9/1/2024	DD	856	874	314	X	X	X	X
6	3	1/1/2024	12/2/2023	CC	99	88	24	X	X	X	X		7	9/1/2024	8/2/2024	GG	888	999	110	X	X	X	X
7	4	10/1/2024	9/1/2024	DD	856	874	314	X	X	X	X		8	11/1/2024	10/2/2024	HH	5687	45874	2365	X	X	X	X
8	5	10/5/2020	9/5/2020	EE	101	202	303	X	X	X	X
9	6	9/9/2020	8/10/2020	FF	444	555	666	X	X	X	X
10	7	9/1/2024	8/2/2024	GG	888	999	110	X	X	X	X
11	8	11/1/2024	10/2/2024	HH	5687	45874	2365	X	X	X	X
Sheet23

Cell Formulas
Range	Formula
M4:W7	M4	=FILTER(ShippingData,YEAR(ShippingData[Shipment Date])=M2)
Dynamic array formulas.

 

[ItalianPlatinum]

Are you able to see what I am doing wrong? it looks good from what you sent but when replicating its not operating for me.

 

[LegendaryAxe]

You need to close the parentheses on the Year function. It should be "YEAR(B3:B11)=$M$2"

Also, there's no need to use an absolute reference in a filter function, so you can change "$M$2" to "M2".

 

[ItalianPlatinum]

Thanks for the quick response but now it shows as an error

 

[Cubist]

The function YEAR() expects a date input. You're grabbing B3 which is a text header. Try starting with row 4.

 

[LegendaryAxe]

Don't include the header row in your range. The year function being used on the header text will cause a #VALUE error.

This should work:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2													2024
3	Job #	Shipment Date	X	X	X	X	X	X	X	X	X		3	45292	X	X	X	X	X	X	X	X	X
4	1	1/1/2023	X	X	X	X	X	X	X	X	X		4	45566	X	X	X	X	X	X	X	X	X
5	2	1/1/2022	X	X	X	X	X	X	X	X	X		7	45536	X	X	X	X	X	X	X	X	X
6	3	1/1/2024	X	X	X	X	X	X	X	X	X		8	45597	X	X	X	X	X	X	X	X	X
7	4	10/1/2024	X	X	X	X	X	X	X	X	X
8	5	10/5/2020	X	X	X	X	X	X	X	X	X
9	6	9/9/2020	X	X	X	X	X	X	X	X	X
10	7	9/1/2024	X	X	X	X	X	X	X	X	X
11	8	11/1/2024	X	X	X	X	X	X	X	X	X
12
13
Sheet1

Cell Formulas
Range	Formula
M3:W6	M3	=FILTER(A4:K11,YEAR(B4:B11)=M2)
Dynamic array formulas.

 

[ItalianPlatinum]

Darn I should have known that. Thanks both - that was helpful and also fits my ask.

 

[ItalianPlatinum]

Back again - have a slight problem. Didn’t realize the computer has excel version 2013 therefore the filter doesn’t work on that workstation. Is there a similar method that can be used in that version?

 

[Cubist]

It's pretty ugly and most of us don't have 2013 to test anymore. I suggest upgrade to 365.
2013 isn't being supported by Microsoft anymore so you're exposing yourself to a lot of vulnerabilities.