Help using FILTER

[Paulypaul]

I'd like to add a dashboard tab to my Excel file, which will display filtered data from rows in another tab, dependent upon their date range (start and finish date) values are still valid for this week (e.g. they are still live/have started but not reached the finished date yet).
The purpose of this is to show management a current list of live actions with their corresponding Task name, status, etc. I have highlighted in yellow the columns I wish to display for each Action row.

I've tried referencing two manually-added start and finish dates in my dashboard (e.g. "29/01/2024" & "04/02/2024"), using this formula..
=FILTER(Actions!A:Q,(ROW(Actions!A:Q)=1)+(A1<=Actions!A:A)*(A2>=Actions!B:B))

I know this formula is both incorrect and doesn't select which columns of data I want to display.

Is anyone able to help me? That would be fantastic. Many thanks in advance.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=FILTER(choosecols(Actions!A2:Q1000,1,2,5,6,8,9,10,16,17),(A1<=Actions!K2:K1000)*(A2>=Actions!L2:L1000))

 

[SanjayGMusafir]

=FILTER(Actions!A:Q,(ROW(Actions!A:Q)=1)+(A1<=Actions!A:A)*(A2>=Actions!B:B))

Welcome to the forum -

Check this and revert -

=Filter(FILTER(Actions!A:Q,(A1<=Actions!A:A)*(A2>=Actions!B:B)),{1,1,0,0,1,1,0,1,1,1,0,0,0,0,0,1,1})

 

[RoryA]

Try:

=CHOOSECOLS(FILTER(Actions!A:Q,(Actions!K:K<=A2)*(Actions!L:L>=A1)),1,2,5,6,8,9,10,16,17)

 

[SanjayGMusafir]

=Filter(FILTER(Actions!A:Q,(A1<=Actions!A:A)*(A2>=Actions!B:B)),{1,1,0,0,1,1,0,1,1,1,0,0,0,0,0,1,1})

Just realised after looking at solutions @Fluff and @RoryA gave
It has to be -

=Filter(FILTER(Actions!A:Q,(A1<=Actions!K:K)*(A2>=Actions!L:L)),{1,1,0,0,1,1,0,1,1,1,0,0,0,0,0,1,1})

 

[JamesCanale]

Pile on

MrExcelPlayground20.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
1																			1/31/2024
2	Action	Area	Junk	Junker	TaskID	Owner	Actor	Project1	Project2	Project3	Start	End	Comments	Statu	Impact	Risk	Mitigation		Action	Area	TaskID	Owner	Project1	Project2	Project3	Risk	Mitigation
3	Title1	Area1	A	A	CM01	Fred	Junk1	check	check	ex	1/29/2024	1/29/2024	blah	bla	yes	Low	Stuff1		Title2	Area2	CM02	Fred	check	check	ex	Low	Stuff2
4	Title2	Area2	A	A	CM02	Fred	Junk2	check	check	ex	1/24/2024	2/19/2024	blah	bla	yes	Low	Stuff2		Title3	Area3	CM03	Fred	check	check	ex	Low	Stuff3
5	Title3	Area3	A	A	CM03	Fred	Junk3	check	check	ex	1/29/2024	2/19/2024	blah	bla	yes	Low	Stuff3		Title5	Area5	CM05	Fred	check	check	ex	Low	Stuff5
6	Title4	Area4	A	A	CM04	Fred	Junk4	check	check	ex	3/4/2024	4/12/2024	blah	bla	yes	Low	Stuff4		Title8	Area8	CM08	Fred	check	check	ex	Low	Stuff8
7	Title5	Area5	A	A	CM05	Fred	Junk5	check	check	ex	1/29/2024	7/1/2024	blah	bla	yes	Low	Stuff5
8	Title6	Area6	A	A	CM06	Fred	Junk6	check	check	ex	2/2/2024	2/8/2024	blah	bla	yes	Low	Stuff6
9	Title7	Area7	A	A	CM07	Fred	Junk7	check	check	ex	2/12/2024	2/12/2024	blah	bla	yes	Low	Stuff7
10	Title8	Area8	A	A	CM08	Fred	Junk8	check	check	ex	1/3/2024	3/8/2024	blah	bla	yes	Low	Stuff8
11	Title9	Area9	A	A	CM09	Fred	Junk9	check	check	ex	3/11/2024	3/11/2024	blah	bla	yes	Low	Stuff9
12	Title10	Area10	A	A	CM10	Fred	Junk10	check	check	ex	4/4/2024	4/12/2024	blah	bla	yes	Low	Stuff10
13	Title11	Area11	A	A	CM11	Fred	Junk11	check	check	ex	4/15/2024	4/15/2024	blah	bla	yes	Low	Stuff11
14	Title12	Area12	A	A	CM12	Fred	Junk12	check	check	ex	5/2/2024	5/10/2024	blah	bla	yes	Low	Stuff12
15	Title13	Area13	A	A	CM13	Fred	Junk13	check	check	ex	5/13/2024	12/1/2024	blah	bla	yes	Low	Stuff13
16	Title14	Area14	A	A	CM14	Fred	Junk14	check	check	ex	5/28/2024	11/1/2024	blah	bla	yes	Low	Stuff14
17	Title15	Area15	A	A	CM15	Fred	Junk15	check	check	ex	6/10/2024	10/1/2024	blah	bla	yes	Low	Stuff15
Sheet28

Cell Formulas
Range	Formula
S1	S1	=TODAY()
S2:AA6	S2	=CHOOSECOLS(VSTACK(A2:Q2,FILTER(A3:Q17,(K3:K17<=S1)*(L3:L17>=S1))),1,2,5,6,8,9,10,16,17)
Dynamic array formulas.

 

[SanjayGMusafir]

Just for my understanding @JamesCanale why would we need VSTACK in this data (structure) and result to be generated.

Thanks

 

[JamesCanale]

I got the impression that they wanted to pull in the column headers. Maybe so it would be easier to change what columns are output in the report. But the question was a bit foggy to me.

 

[Paulypaul]

Hi & welcome to MrExcel.
How about

=FILTER(choosecols(Actions!A2:Q1000,1,2,5,6,8,9,10,16,17),(A1<=Actions!K2:K1000)*(A2>=Actions!L2:L1000))

Hi.
I switched the greater than/lesser than signs and it worked perfectly. I really appreciate that. Thank you very much.

Is there a way I can only display the Actions that are in 1 of the 3 projects (columns H, I or J)?
That way I can create 3 versions of the formula, allowing me to display actions from the 3 project separately.

Many thanks again.

 

[RoryA]

The 1,2,5,6,8,9,10,16,17 part is the column numbers to return data from, so you can add/remove those as needed.