Two Books communicating

[daren141]

i have two seperate excel workbooks that i want to communicate with each other. one will have info from the other, I got one column to work im just having issue with the other column. I want one column (H) to do one of two things, if you can provide a solution to both that would be appreciated. you can see the formula I used for the other but it didn't work.
1 return the names of the jobs, if its 1 job (Name1), if its two jobs (Name1, Name2) etc.
2 return the number of times the job is mentioned, if its 1 job (1) if its 2 jobs (2).
Hopefully that makes sense.
Thank you.

Book1

Monday.com Upload.xlsx
	A	B	C	D	E	F	G	H
1	Created: 9/7/2023 3:37:02 PM
2
3	ID	OrderNo	RequiredDate	ShipTO_Name	ShipVia	PurchaseEMail
4	Company name	Job	Ship Date	Job Name	Sales Person	Email	P1	WHS
5		20505	9/5/2023				No
6		20506	9/8/2023				No
7		20507	10/20/2023				No
8		20508	9/5/2023				No
9		20509	9/6/2023				No
10		20510	9/6/2023				No	#N/A
11		20511	9/6/2023				No	#N/A
12		20512	9/6/2023				No	#N/A
13		20513	9/7/2023				No	#N/A
14		20514	9/16/2023				No	#N/A
15		20515	7/31/2023				No	#N/A
16		20516	9/7/2023				No	#N/A
17		20517	9/7/2023				No	#N/A
18		20518	9/7/2023				No	#N/A
19		20519	9/7/2023				No	#N/A
20		20520	11/1/2023				Yes	#N/A
expAE6C

Cell Formulas
Range	Formula
G5:G20	G5	=IF(B5:B20='[Monday.com Wekly Report.xlsx]P1'!$C$2, "Yes", "No")
H5:H20	H5	=IF(B5:B20='[Monday.com Wekly Report.xlsx]LQD'!$C$2:$C$6, '[Monday.com Wekly Report.xlsx]LQD'!$A$2, "")
Dynamic array formulas.

Book2

Monday.com Wekly Report.xlsx
	A	B	C
1	ID	ID_1	OrderNo
2	March-NV	Johnson	20514
3	NYC	Johnson	20514
4	BOOM	VOLKS	20520
5	South	VOLKS	20520
6	March	VOLKS	20520
LQD

 

[Alex Blakenburg]

There is probably more to it than this but see if this is what you mean.

20230910 Linked Workbooks daren141.xlsx
	A	B	C	D	E	F	G	H	I
1	Created: 9/7/2023 3:37:02 PM
2
3	ID	OrderNo	RequiredDate	ShipTO_Name	ShipVia	PurchaseEMail
4	Company name	Job	Ship Date	Job Name	Sales Person	Email	P1	WHS	WHS Count
5		20505	9/05/2023				No		1
6		20506	9/08/2023				No		1
7		20507	10/20/2023				No		1
8		20508	9/05/2023				No		1
9		20509	9/06/2023				No		1
10		20510	9/06/2023				No		1
11		20511	9/06/2023				No		1
12		20512	9/06/2023				No		1
13		20513	9/07/2023				No		1
14		20514	9/16/2023				Yes	March-NV, NYC	2
15		20515	7/31/2023				No		1
16		20516	9/07/2023				No		1
17		20517	9/07/2023				No		1
18		20518	9/07/2023				No		1
19		20519	9/07/2023				No		1
20		20520	11/01/2023				No	BOOM, South, March	3
expAEC

Cell Formulas
Range	Formula
G5:G20	G5	=IF(B5:B20='[Monday.com Wekly Report.xlsx]LQD'!$C$2, "Yes", "No")
H5:H20	H5	=TEXTJOIN(", ",TRUE,FILTER('[Monday.com Wekly Report.xlsx]LQD'!$A$2:$A$6,'[Monday.com Wekly Report.xlsx]LQD'!$C$2:$C$6=$B5,""))
I5:I20	I5	=COUNTA(FILTER('[Monday.com Wekly Report.xlsx]LQD'!$A$2:$A$6,'[Monday.com Wekly Report.xlsx]LQD'!$C$2:$C$6=$B5,""))
Dynamic array formulas.