Populate Data Based on Criteria

[kumara_faith]

Hi,

I have the following table in sheet 1:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	Table 1
3	Month	Date	Branch	Staff	Task1	Task2	Task3	Task4	Task5	Task6	Task7	Task8	Task9	Task10	Task11	Task12	Task13	Task14	Task15	Task16	Task17	Task18
4	Jan	Monday, 2 January, 2023	Texas	Staff1	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
5	Jan	Tuesday, 3 January, 2023	New York	Staff2	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
6	Jan	Wednesday, 4 January, 2023	Oklahoma	Staff3	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
7	Feb	Wednesday, 1 February, 2023	Texas	Staff1	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
8	Feb	Thursday, 2 February, 2023	New York	Staff2	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
9	Feb	Friday, 3 February, 2023	Oklahoma	Staff3	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
10	Mar	Wednesday, 1 March, 2023	Texas	Staff1	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
11	Mar	Thursday, 2 March, 2023	New York	Staff2	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
12	Mar	Friday, 3 March, 2023	Oklahoma	Staff3	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
Sheet1

In Sheet 2, I have the following table where I would likeusers can enter the crietria:

Book1
	B	C	D	E	F
15	Table 2
16	Month	Branch	Staff	Start Date	End Date
17	Jan	Texas	Staff1	2/1/2023	3/3/2023
Sheet1

The correct result should populate as below in Table 3:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
20	Table 3
21	Month	Date	Branch	Staff	Task1	Task2	Task3	Task4	Task5	Task6	Task7	Task8	Task9	Task10	Task11	Task12	Task13	Task14	Task15	Task16	Task17	Task18
22	Jan	Monday, 2 January, 2023	Texas	Staff1	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
23	Feb	Wednesday, 1 February, 2023	Texas	Staff1	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
24	Mar	Wednesday, 1 March, 2023	Texas	Staff1	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10	10
Sheet1

Is there a way to build formula in Table 3 to populate data based on the criterias entered in Table 2 in Table 3? Appreciate all the help.

 

[Fluff]

How about

=FILTER(B4:W12,(C4:C12>=E17)*(C4:C12<+F17)*(D4:D12=C17)*(E4:E12=D17))

 

[kumara_faith]

Thank you Fluff. That worked. Have a great day.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kumara_faith]

Hi Fluff,

If I have the similar table in sheet 1, sheet2, sheet3:

Book2
	B	C	D	E	F	G	H	I	J
2		Sheet1
3	Month	Date	Branch	Staff	Task1	Task2	Task3	Task4	Task5
4	Jan	1/1/2023	Texas	Javier	10	10	10	10	10
5	Jan	1/1/2023	Ohio	Michael	10	10	10	10	10
6	Jan	1/1/2023	New York	Jenny	10	10	10	10	10
7	Jan	1/1/2023	Virginia	Paulina	10	10	10	10	10
8	Jan	1/1/2023	Colorado	Noell	10	10	10	10	10
9	Jan	2/1/2023	Texas	Javier	10	10	10	10	10
10	Jan	2/1/2023	Ohio	Michael	10	10	10	10	10
11	Jan	2/1/2023	New York	Jenny	10	10	10	10	10
12	Jan	2/1/2023	Virginia	Paulina	10	10	10	10	10
13	Jan	3/1/2023	Colorado	Noell	10	10	10	10	10
14
15
16		Sheet2
17	Month	Date	Branch	Staff	Task1	Task2	Task3	Task4	Task5
18	Feb	1/2/2023	Texas	Javier	10	10	10	10	10
19	Feb	1/2/2023	Ohio	Michael	10	10	10	10	10
20	Feb	1/2/2023	New York	Jenny	10	10	10	10	10
21	Feb	1/2/2023	Virginia	Paulina	10	10	10	10	10
22	Feb	1/2/2023	Colorado	Noell	10	10	10	10	10
23	Feb	2/2/2023	Texas	Javier	10	10	10	10	10
24	Feb	2/2/2023	Ohio	Michael	10	10	10	10	10
25	Feb	2/2/2023	New York	Jenny	10	10	10	10	10
26	Feb	2/2/2023	Virginia	Paulina	10	10	10	10	10
27	Feb	3/2/2023	Colorado	Noell	10	10	10	10	10
28
29
30		Sheet3
31	Month	Date	Branch	Staff	Task1	Task2	Task3	Task4	Task5
32	Mar	3/3/2023	Texas	Javier	10	10	10	10	10
33	Mar	3/3/2023	Ohio	Michael	10	10	10	10	10
34	Mar	3/3/2023	New York	Jenny	10	10	10	10	10
35	Mar	3/3/2023	Virginia	Paulina	10	10	10	10	10
36	Mar	3/3/2023	Colorado	Noell	10	10	10	10	10
37	Mar	4/3/2023	Texas	Javier	10	10	10	10	10
38	Mar	4/3/2023	Ohio	Michael	10	10	10	10	10
39	Mar	4/3/2023	New York	Jenny	10	10	10	10	10
40	Mar	4/3/2023	Virginia	Paulina	10	10	10	10	10
41	Mar	5/3/2023	Colorado	Noell	10	10	10	10	10
Sheet1

In Sheet4, I have Table 1 where users can enter the criteria and Table 2 where the data populates automatically. The correct result is in table 2 based on the criteria in table 1. I tried modfying the earlier Filter solution but it did not work. Will Filter fundtion not work across multiple sheet ? Appreciate the help:

Book2
	M	N	O	P	Q	R	S	T	U
2	Table 1: Enter Criteria
3	Month	Branch	Staff	Start Date	End Date
4	Jan	Texas	Javier	1/1/2023	5/3/2023
5
6
7	Table 2: Result
8	Month	Date	Branch	Staff	Task1	Task2	Task3	Task4	Task5
9	Jan	1/1/2023	Texas	Javier	10	10	10	10	10
10	Jan	2/1/2023	Texas	Javier	10	10	10	10	10
11	Feb	1/2/2023	Texas	Javier	10	10	10	10	10
12	Feb	2/2/2023	Texas	Javier	10	10	10	10	10
13	Mar	3/3/2023	Texas	Javier	10	10	10	10	10
14	Mar	4/3/2023	Texas	Javier	10	10	10	10	10
Sheet1

 

[Fluff]

You would be far better off keeping all the data in one sheet, it makes analysing it much easier.

 

[kumara_faith]

Hi Fluff,

I absolutely agree. Unfortunately due to the user requirements, the file is currently set up in such a way. Is there a way to build the search table across multiple sheets ?

 

[Fluff]

Do you have the VSTACK function?

 

[kumara_faith]

Yes, I do.

 

[Fluff]

Ok, how about

=LET(v,VSTACK(Sheet1:Sheet3!B4:W12),FILTER(v,(INDEX(v,,2)>=E17)*(INDEX(v,,2)<=F17)*(INDEX(v,,3)=C17)*(INDEX(v,,4)=D17)))

but all 3 sheets must be consecutive sheets