Use Dropdown to search other tabs

[SFCChase]

Is it possible to search across multiple tabs in a workbook based off a dropdown box?

I have the following "wrap-up" page with a dropdown in the cell below unit (cell A2). I have about 40 additional tabs (each dedicated to an employee). I would like to be able to select a unit in cell A2 and have the workbook search each tab and if the employees assigned unit matches, sum the hours scheduled/worked in cells C5:E31.

UNIT
PAY PERIOD	ON CALL SCHEDULED	ON CALL WORKED	TOTAL
TO	FROM
09/24/23	10/07/23	0	0	0
10/08/23	10/21/23	0	0	0
10/22/23	11/04/23	0	0	0
11/05/23	11/18/23	0	0	0
11/19/23	12/02/23	0	0	0
12/03/23	12/16/23	0	0	0
12/17/23	12/30/23	0	0	0
12/31/23	01/13/24	0	0	0
01/14/24	01/27/24	0	0	0
01/28/24	02/10/24	0	0	0
02/11/24	02/24/24	0	0	0
02/25/24	03/09/24	0	0	0
03/10/24	03/23/24	0	0	0
03/24/24	04/06/24	0	0	0
04/07/24	04/20/24	0	0	0
04/21/24	05/04/24	0	0	0
05/05/24	05/18/24	0	0	0
05/19/24	06/01/24	0	0	0
06/02/24	06/15/24	0	0	0
06/16/24	06/29/24	0	0	0
06/30/24	07/13/24	0	0	0
07/14/24	07/27/24	0	0	0
07/28/24	08/10/24	0	0	0
08/11/24	08/24/24	0	0	0
08/25/24	09/07/24	0	0	0
09/08/24	09/21/24	0	0	0
09/22/24	10/05/24	0	0	0

I have been playing with LOOKUP and SUMIF formulas but can't get anything to work. Any help would be appreciated. I'm not very familiar with VBA or Macros, but willing to learn if somebody has suggestions.

 

[Jeffrey Mahoney]

Here is a rough start. I just looked at your OFFICE version. You may not have all the functions, but I'm going to post anyway cause I worked on it.

As you can see, I culminated the data from 2 sheets into columns starting at J5 through M5. This could be done for many more sheets as long as they are grouped together. Now I can use typical functions like Sumifs to get the data from those columns. Notice that in each person's sheet I added the person's name in each row of the working dates.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	UNIT
2	John
3	PAY PERIOD	ON CALL SCHEDULED	ON CALL WORKED	Regular	TOTAL
4	TO	FROM								Name	Dates	On Call	Regular
5	9/24/2023	10/7/2023	87	112	199					Pete	9/24/2023	8	8
6	10/8/2023	10/21/2023	0	24	0					Pete	9/25/2023	6	8
7	10/22/2023	11/4/2023	0	0	0					Pete	9/26/2023	8	8
8	11/5/2023	11/18/2023	0	0	0					Pete	9/27/2023	8	8
9	11/19/2023	12/2/2023	0	0	0					Pete	9/28/2023	8	8
10	12/3/2023	12/16/2023	0	0	0					Pete	9/29/2023	9	8
11	12/17/2023	12/30/2023	0	0	0					Pete	9/30/2023	9	8
12	12/31/2023	1/13/2024	0	0	0					Pete	10/1/2023	9	8
13	1/14/2024	1/27/2024	0	0	0					Pete	10/2/2023	10	8
14	1/28/2024	2/10/2024	0	0	0					Pete	10/3/2023	10	8
15	2/11/2024	2/24/2024	0	0	0					Pete	10/4/2023	10	8
16	2/25/2024	3/9/2024	0	0	0					Pete	10/5/2023	5	8
Sheet2

Cell Formulas
Range	Formula
C5	C5	=SUMIFS($L$5#,$J$5#,$A$2,$K$5#,">="&$A5,$K$5#,"<="&$B5)
D5:D16	D5	=SUMIFS($M$5#,$J$5#,$A$2,$K$5#,">="&$A5,$K$5#,"<="&$B5)
E5	E5	=SUM(C5:D5)
J5:M58	J5	=VSTACK(Pete:John!B5:B31)
C6:C16	C6	=SUMIFS($L$5#,$J$5#,A3,$K$5#,">="&A6,$K$5#,"<="&B6)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2	List	Pete,John

Book2
	A	B	C	D	E
1
2
3
4		Name	Dates	On Call	Regular
5		Pete	9/24/2023	8	8
6		Pete	9/25/2023	6	8
7		Pete	9/26/2023	8	8
8		Pete	9/27/2023	8	8
9		Pete	9/28/2023	8	8
10		Pete	9/29/2023	9	8
11		Pete	9/30/2023	9	8
12		Pete	10/1/2023	9	8
13		Pete	10/2/2023	10	8
14		Pete	10/3/2023	10	8
15		Pete	10/4/2023	10	8
16		Pete	10/5/2023	5	8
17		Pete	10/6/2023	5	8
18		Pete	10/7/2023	5	8
19		Pete	10/8/2023	8	8
20		Pete	10/9/2023	8	8
21		Pete	10/10/2023	8	8
22
Pete

 

[SFCChase]

Here is a rough start. I just looked at your OFFICE version. You may not have all the functions, but I'm going to post anyway cause I worked on it.

As you can see, I culminated the data from 2 sheets into columns starting at J5 through M5. This could be done for many more sheets as long as they are grouped together. Now I can use typical functions like Sumifs to get the data from those columns. Notice that in each person's sheet I added the person's name in each row of the working dates.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	UNIT
2	John
3	PAY PERIOD	ON CALL SCHEDULED	ON CALL WORKED	Regular	TOTAL
4	TO	FROM								Name	Dates	On Call	Regular
5	9/24/2023	10/7/2023	87	112	199					Pete	9/24/2023	8	8
6	10/8/2023	10/21/2023	0	24	0					Pete	9/25/2023	6	8
7	10/22/2023	11/4/2023	0	0	0					Pete	9/26/2023	8	8
8	11/5/2023	11/18/2023	0	0	0					Pete	9/27/2023	8	8
9	11/19/2023	12/2/2023	0	0	0					Pete	9/28/2023	8	8
10	12/3/2023	12/16/2023	0	0	0					Pete	9/29/2023	9	8
11	12/17/2023	12/30/2023	0	0	0					Pete	9/30/2023	9	8
12	12/31/2023	1/13/2024	0	0	0					Pete	10/1/2023	9	8
13	1/14/2024	1/27/2024	0	0	0					Pete	10/2/2023	10	8
14	1/28/2024	2/10/2024	0	0	0					Pete	10/3/2023	10	8
15	2/11/2024	2/24/2024	0	0	0					Pete	10/4/2023	10	8
16	2/25/2024	3/9/2024	0	0	0					Pete	10/5/2023	5	8
Sheet2

Cell Formulas
Range	Formula
C5	C5	=SUMIFS($L$5#,$J$5#,$A$2,$K$5#,">="&$A5,$K$5#,"<="&$B5)
D5:D16	D5	=SUMIFS($M$5#,$J$5#,$A$2,$K$5#,">="&$A5,$K$5#,"<="&$B5)
E5	E5	=SUM(C5:D5)
J5:M58	J5	=VSTACK(Pete:John!B5:B31)
C6:C16	C6	=SUMIFS($L$5#,$J$5#,A3,$K$5#,">="&A6,$K$5#,"<="&B6)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2	List	Pete,John

Book2
	A	B	C	D	E
1
2
3
4		Name	Dates	On Call	Regular
5		Pete	9/24/2023	8	8
6		Pete	9/25/2023	6	8
7		Pete	9/26/2023	8	8
8		Pete	9/27/2023	8	8
9		Pete	9/28/2023	8	8
10		Pete	9/29/2023	9	8
11		Pete	9/30/2023	9	8
12		Pete	10/1/2023	9	8
13		Pete	10/2/2023	10	8
14		Pete	10/3/2023	10	8
15		Pete	10/4/2023	10	8
16		Pete	10/5/2023	5	8
17		Pete	10/6/2023	5	8
18		Pete	10/7/2023	5	8
19		Pete	10/8/2023	8	8
20		Pete	10/9/2023	8	8
21		Pete	10/10/2023	8	8
22
Pete

Thanks Jeff....I'm having some issues viewing any data you put in for "John". Is there something missing from the picture?

Also....can you please explain what the # means in your equations or is that just a holder for me to input my specific cell references?

 

[Jeffrey Mahoney]

Sorry, I didn't post the John sheet. Just duplicate the Pete sheet and change some of the hours for simulation

I'm hoping you have the version of Excel to run the functions I gave you.

# is used to reference array formulas. This saves time from having to specify a range that may change in size depending on how many rows your array formula is going to retrieve. In this case cell J6 has an array formula

 

[SFCChase]

Sorry, I didn't post the John sheet. Just duplicate the Pete sheet and change some of the hours for simulation

I'm hoping you have the version of Excel to run the functions I gave you.

# is used to reference array formulas. This saves time from having to specify a range that may change in size depending on how many rows your array formula is going to retrieve. In this case cell J6 has an array formula

No worries. I ended up figuring it out.

My excel will allow me to use the VSTACK function, but it wouldn't let me use the # to close the array. I ended up having to type i the full ranges, but it ended up working out anyway. Thanks for the help!