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.