What is the solution?

[Noxqss38242]

I have a workbook that needs updated by several people but as you know only 1 person can be in the file at a time.
Is there a way to create something that I can pull all the information from other workbooks (so each department can have their own) yet format it to summarize all departments in another workbook/sheet?

Attached is what the summary page looks like now (but that everyone updates), I want this summary page to auto-adjust as well. If the person over "Murray" adds rows or subtracts rows the summary page needs to grab all the information. Is this possible either in excel or google sheets or ??? VBA?

maintacc-NEW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	PTL TRUCK DOWN REPORT									04/25/23
2	Total trucks down											OEM Total Out of Service at Dealer %
3						# Trucks	Trucks > 24 Hours	Days Down	Avg/Truck					Total Units	# O/S	# Days	Avg	Down %
4	Occupied Waiting				Murray Shop	7	0	7	1.0				Freightliner	634	7	26	3.71	1.10%
5					West Memphis	5	2	11	2.2				International	424	16	62	3.88	3.77%
6					Indianapolis	2	0	2	1.0
7					Terminal Vendor	5	4	17	3.4			OEM Out of Service - Road Dealer %
8					Road	10	5	24	2.4					Total Units	# O/S	# Days	Avg	Down %
9													Freightliner	634	5	12	2.40	0.79%
10	Total Occupied Down for Maintenance					29	11	61	2.1				International	424	5	12	2.40	1.18%
11	Total Occupied Down for Maintenance %					2.74%
12									Avg/Truck			OEM Out of Service - Terminal Dealer %
13	Empty check out				Murray Shop	11	9	139	12.6					Total Units	# O/S	# Days	Avg	Down %
14					West Memphis	11	8	48	4.4				Freightliner	634	2	14	7.00	0.32%
15					Indianapolis	4	3	100	25.0				International	424	11	50	4.55	2.59%
16					Terminal Vendor	8	6	72	9.0
17					Road	0	0	0	0
18
19	Total Empty					34	26	359	10.6
20	Total Empty %					3.2%
21
22								Days Down	Avg/Truck
23	Accidents in Process					3		116	38.7
24
25	Total down					66
26
27	Total Active Trucks					1058
28
29	Active Truck Down %					6.24%		Avail %	93.76%
30
31	Murray Shop Occupied												TOTAL:	7		Over 24 hours:	0
32	UNIT	DAYS	Total Days O/S	MILES	STATUS			ETA		NOTES								Date in Status	Date O/S
33	21088	1	1	62725	OCC					pm / annual								4/24/2023	4/24/2023
34	16938	1	1	322550	OCC					IPM								4/24/2023	4/24/2023
35	21103	1	1	109197	OCC					geotab								4/24/2023	4/24/2023
36	17048	1	1	162620	OCC					misc repairs								4/24/2023	4/24/2023
37	21005	1	1	194026	OCC					misc repairs								4/24/2023	4/24/2023
38	17178	1	1	121577	OCC					misc repairs								4/24/2023	4/24/2023
39	17207	1	1	126085	OCC					oil leak								4/24/2023	4/24/2023
40
41	West Memphis Occupied												TOTAL:	5		Over 24 hours:	2
42	UNIT	DAYS	Total Days O/S	MILES	STATUS			ETA		NOTES								Date in Status	Date O/S
43	20969	5	5	352752	OCC					IPM - pending parts, due today								4/20/2023	4/20/2023
44	17250	3	3	177757	OCC					misc repairs - driver home until 4/25								4/22/2023	4/22/2023
45	17091	1	1	215817	OCC					hvac								4/24/2023	4/24/2023
46	20888	1	1	363272	OCC					misc repairs								4/24/2023	4/24/2023
47	20842	1	1	357363	OCC					misc repairs								4/24/2023	4/24/2023
48
49	Indianapolis Occupied												TOTAL:	2		Over 24 hours:	0
50	UNIT	DAYS	Total Days O/S	MILES	STATUS			ETA		NOTES								Date in Status	Date O/S
51	20723	1	1	361735	OCC					last chance inspection								4/24/2023	4/24/2023
52	17292	1	1	7297	OCC					engine warning light								4/24/2023	4/24/2023
53
54	Terminal Vendors Occupied				0	5							TOTAL:	5	0	Over 24 hours:	4
55	UNIT	DAYS AT DEALER	TOTAL DAYS O/S	MILES	DEALER GROUP		CITY/ST	ETA	EST	COMPLAINT	16	0			17			DATE IN STATUS	DATE O/S
56	17144	6	7	131720	Rush		Indianapolis, IN	4/25	non-war	water in diff, overhead								4/19/2023	4/18/2023
57	17161	3	3	237080	Rotex		Laredo, TX	4/25	war	abs codes				pnd parts-brake switch				4/22/2023	4/22/2023
58	16955	3	3	324455	Rotex		Laredo, TX	4/25	war	ac compressor				pnd parts-compressor/condensor				4/22/2023	4/22/2023
59	17253	3	3	32055	Rush		Memphis, TN	n/a	non-war	fuel rail codes - excessive water in tanks								4/22/2023	4/22/2023
60	16933	1	1	385454	Rush		Indianapolis, IN	n/a	war	turbo codes								4/24/2023	4/24/2023
61
TRUCKS

Cell Formulas
Range	Formula
J1	J1	=TODAY()
F4	F4	=N31
G4	G4	=Q31
H4	H4	=SUM(C33:C40)
I23,I19,I13:I17,I10,I4:I8	I4	=IFERROR(H4/F4,0)
F5	F5	=N41
G5	G5	=Q41
H5	H5	=SUM(C43:C48)
F6	F6	=N49
G6	G6	=Q49
H6	H6	=SUM(C51:C53)
F7	F7	=N54
G7	G7	=Q54
H7	H7	=SUM(C56:C60)
F8	F8	=N62
G8	G8	=Q62
H8	H8	=SUM(C64:C73)
O4:P5	O4	=SUM(O9,O14)
Q14:Q15,Q9:Q10,Q4:Q5	Q4	=IFERROR(P4/O4,0)
R14:R15,R9:R10,R4:R5	R4	=O4/N4
N9:N10	N9	=N4
O9	O9	=E62+E122
P9:P10	P9	=O62+O122
O10	O10	=F62+F122
F19:H19,F10:H10	G10	=SUM(G4:G8)
F11	F11	=F10/F27
F13	F13	=N75
G13	G13	=Q75
H13	H13	=SUM(C77:C87)
F14	F14	=N89
G14	G14	=Q89
H14	H14	=SUM(C91:C101)
F15	F15	=N103
G15	G15	=Q103
H15	H15	=SUM(C105:C110)
F16	F16	=N111
G16	G16	=Q111
H16	H16	=SUM(C113:C120)
F17	F17	=N122
G17	G17	=Q122
H17	H17	=SUM(C124:C125)
N14:N15	N14	=N4
O14	O14	=E54+E111
P14	P14	=L55+L112
O15	O15	=F54+F111
P15	P15	=K55+K112
F20	F20	=F19/F27
F23	F23	=N127
H23	H23	=SUM(C129:C131)
F25	F25	=SUM(F10,F19,F23)
F27	F27	=N4+N5
F29	F29	=F25/F27
I29	I29	=100%-F29
N31	N31	=COUNTA(A33:A40)
Q31	Q31	=COUNTIF(C33:C40,">1")
B51:C52,B43:C47,B33:C39	B33	=$J$1-R33
N41	N41	=COUNTA(A43:A48)
Q41	Q41	=COUNTIF(C43:C48,">1")
N49	N49	=COUNTA(A51:A53)
Q49	Q49	=COUNTIF(C51:C53,">1")
E54	E54	=COUNTIF(A56:A60,">19000")
F54	F54	=COUNTIF(A56:A60,"<19000")
N54	N54	=COUNTA(A56:A60)
Q54	Q54	=COUNTIF(C56:C60,">1")
K55	K55	=SUMIF(A56:A60,"<19000",B56:B60)
L55	L55	=SUMIF(A56:A60,">19000",B56:B60)
O54	O54	=SUMIF(A56:A60,">19000",C56:C60)
O55	O55	=SUMIF(A56:A60,"<19000",C56:C60)
B56:C60	B56	=TODAY()-R56

 

[RoryA]

Power Query/Get and transform is probably the best option for consolidating workbooks.

 

[Noxqss38242]

This will work, it takes some getting used to but I can make this work! Thank you!