Trying to use a formula in one workbook to sum data of multiple rows with duplicate names in another workbook. Getting reference error.

[Tmcgrew05]

Trying to use a formula in one workbook to sum data of multiple rows with duplicate names in another workbook. I tried to link the 2 workbooks but failed. I tried to use a macro to open the other workbook...failed. My formula gives #ref! error. Please help. I know there is an easy way to do this but having a brain fart.

FEC 2

BAY 1

985B/241B/881A/051A LENS-BEZEL ASSY

$1,736​

$2​

1​

8​

1/4/1900​

2​

0.5​

0​

2.5​

1​

2.5​

4​

########​

TRUE​

FFV 1

BAY 1

010B CHL AFS/HG/SPORT, LH/RH

$1,940​

3​

7.5​

2.8​

0​

0​

2.8​

1​

2.8​

4.4​

########​

TRUE​

FGB 1

BAY 1

985B/241B Base HL

$3,353​

$5​

8​

5​

0​

0​

5​

1​

5​

7.9​

########​

TRUE​

FSA FGB 2

BAY 1

985B/241B REFL B SUBASY

$3,321​

$3​

1​

8​

1/8/1900​

3​

1​

0​

4​

1​

4​

6.3​

########​

TRUE​

FAQ

BAY 2

150A LH FOG

$1,205​

2​

8​

2​

0​

0​

2​

1​

2​

3.2​

########​

FALSE​

FAQ 2

BAY 2

150A RH FOG

$1,237​

2​

8​

2​

0​

0​

2​

1​

2​

3.2​

########​

TRUE​

FBD 2

BAY 2

AUTOMATED PES FL

$501​

$2​

4​

1​

0​

0​

1​

1​

1​

1.6​

$616.20​

FALSE​

FFN A

BAY 2

THRA/T6ZA/TGSX FTSL, RH

$-

0​

0​

0​

0​

1​

0​

0​

$-

TRUE​

FFN B

BAY 2

THRA/T6ZA/TGSX FTSL, LH

$-

0​

0​

0​

0​

1​

0​

0​

$-

TRUE​

FGH 1

BAY 2

P375 HL,LH

$4,500​

4​

9.5​

4.8​

0​

0​

4.8​

1​

4.8​

7.5​

########​

TRUE​

FGH 2

BAY 2

P375 HL,RH

$4,305​

$4​

9.5​

4.8​

0​

0​

4.8​

1​

4.8​

7.5​

########​

TRUE​

FGQ 1

BAY 2

U540(2)/U540 LENS/BEZEL SUB

$54​

1​

1/1/1900​

0​

0.2​

0​

0.2​

1​

0.2​

0.3​

$100.13​

FALSE​

Plant PCN

Process

Department

Work Center

Part No

Part Name

Operation No

Operation Code

Prod Date

Shift

Days Ago

Production Work Day

Good Produced

Cost

Earned Value

2.5​

4​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FAQ 2

1NA 947 439-66

989A FL,TMMBC,RH

10​

Assemble Toyota OEM FG

3/1/2022​

1/1/1900​

1​

1​

384​

1.5936​

611.9424​

2.8​

4.4​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FAN

945 230-25

REFLECTOR,PES ASSY,881A CHL,LH

10​

Sub Assemble

3/1/2022​

1/1/1900​

1​

1​

98​

2.5763​

252.4774​

5​

7.9​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FFP 2

948 872-38

DRL SUBASSY,P375 CHL-LED,HG,RH

10​

Sub Assemble

3/1/2022​

1/1/1900​

1​

1​

128​

4.0306​

515.9168​

4​

6.3​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FFP 2

948 872-37

DRL SUBASSY,P375 CHL-LED,HG,LH

10​

Sub Assemble

3/1/2022​

1/1/1900​

1​

1​

64​

4.0306​

257.9584​

FLA

Assembly

Assembly (11319)

FHF 2

10110-7R01F

UNIT ASSY A,PES,HOT,TGVA CHL-LED,LH

10​

Sub Assemble

3/1/2022​

1/1/1900​

1​

1​

195​

3.0165​

588.2175​

2​

3.2​

########​

FALSE​

FLA

Assembly

Assembly (11319)

FHF 2

10110-7R01F

UNIT ASSY A,PES,HOT,TGVA CHL-LED,LH

10​

Sub Assemble

3/1/2022​

1/3/1900​

1​

1​

164​

3.0165​

494.706​

2​

3.2​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FHD 2

10110-7R01F

UNIT ASSY A,PES,HOT,TGVA CHL-LED,LH

10​

Sub Assemble

3/1/2022​

1/1/1900​

1​

1​

366​

3.0165​

1104.039​

1​

1.6​

$616.20​

FALSE​

FLA

Assembly

Assembly (11319)

FHF 3

10010-7R01F

UNIT ASSY A,PES,HOT,TGVA CHL-LED,RH

10​

Sub Assemble

3/1/2022​

1/2/1900​

1​

1​

305​

3.0165​

920.0325​

0​

0​

$-

TRUE​

FLA

Assembly

Assembly (11319)

FHF 3

10010-7R01F

UNIT ASSY A,PES,HOT,TGVA CHL-LED,RH

10​

Sub Assemble

3/1/2022​

1/1/1900​

1​

1​

245​

3.0165​

739.0425​

0​

0​

$-

TRUE​

FLA

Assembly

Assembly (11319)

FHF 3

10010-7R01F

UNIT ASSY A,PES,HOT,TGVA CHL-LED,RH

10​

Sub Assemble

3/1/2022​

1/3/1900​

1​

1​

150​

3.0165​

452.475​

4.8​

7.5​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FGB 1

9TY 945 292-12

241B CHL-LED,BG,SERVICE,RH

10​

Assemble

3/1/2022​

1/1/1900​

1​

1​

17​

14.5054​

246.5918​

4.8​

7.5​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FAQ

1NA 947 439-55

774B FL,LH

10​

Assemble Toyota OEM FG

3/1/2022​

1/3/1900​

1​

1​

16​

1.5936​

25.4976​

0.2​

0.3​

$100.13​

FALSE​

FLA

Assembly

Assembly (11319)

FAQ

1NA 947 439-55

774B FL,LH

10​

Assemble Toyota OEM FG

3/1/2022​

1/1/1900​

1​

1​

356​

1.5936​

567.3216​

FLA

Assembly

Assembly (11319)

FAQ 2

1NA 947 439-56

774B FL,RH

10​

Assemble Toyota OEM FG

3/1/2022​

3​

1​

1​

32​

1.5936​

50.9952​

6​

9.5​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FAQ 2

1NA 947 439-56

774B FL,RH

10​

Assemble Toyota OEM FG

3/1/2022​

1​

1​

1​

360​

1.5936​

573.696​

6​

9.5​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FSA MOLD 3

945 271-43

LENS/BEZEL SUBASSY,010B CHL,HG,LH

10​

Sub Assemble

3/1/2022​

1​

1​

1​

33​

9.4527​

311.9391​

3.5​

5.5​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FSA MOLD 3

945 271-43

LENS/BEZEL SUBASSY,010B CHL,HG,LH

10​

Sub Assemble

3/1/2022​

2​

1​

1​

51​

9.4527​

482.0877​

3.5​

5.5​

########​

TRUE​

FLA

Assembly

Assembly (11319)

FSA MOLD 3

945 271-44

LENS/BEZEL SUBASSY,010B CHL,HG,RH

10​

Sub Assemble

3/1/2022​

2​

1​

1​

60​

9.4527​

567.162​

0.8​

1.3​

$508.37​

TRUE​

PERSONAL.xlsx A 1 Sheet3

 

[Proulxs]

Since you didn't provide the formula I'll assume the formula is right but the reference is the issue. With both files open if you try a simple = then click a cell in the other workbook does the link work? If not it might be because the filename is not recognized. For example if there is a # in the path or filename it could do that.

If you can reference the file this way you could compare what is different in your formula vs the new one.
As a final tip; if you create a new sheet on the workbook holding the data and you try your formula does it work? You can confirm this way that the formula is working as intended or not. And then move that new sheet into a new workbook and see if that break it... Hope this help!

 

[Tmcgrew05]

I am using a sumif formula. The formula works until I close both workbooks and then re-open the destination workbook. I've tried to use a macro to open the source workbook at the same time but I cannot get it work. ? I also just tried to put all in new workbook and rename...failure. And when I open I get the below msg. I tried my macro in new workbook as well and it did not open my dump file either. I do not know what I am doing wrong.

PERSONAL.xlsx
	A	B	C	D				H	I	J	K	L	M	N	O	P	Q	R	S
1	Work Center	SUPV	Desc	Date				Hour worked			Monthly Act.					Total Ops	Semi Direct	Added Value Target	G/NG
2				3/1/2022				1st.	2nd.	3rd.	1st.	2nd.	3rd.	HC	Build Days
1

 

[Tmcgrew05]

I am using a sumif formula. The formula works until I close both workbooks and then re-open the destination workbook. I've tried to use a macro to open the source workbook at the same time but I cannot get it work. ? I also just tried to put all in new workbook and rename...failure. And when I open I get the below msg. I tried my macro in new workbook as well and it did not open my dump file either. I do not know what I am doing wrong.

View attachment 59345

PERSONAL.xlsx
	A	B	C	D				H	I	J	K	L	M	N	O	P	Q	R	S
1	Work Center	SUPV	Desc	Date				Hour worked			Monthly Act.					Total Ops	Semi Direct	Added Value Target	G/NG
2				3/1/2022				1st.	2nd.	3rd.	1st.	2nd.	3rd.	HC	Build Days
1

PERSONAL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Work Center	SUPV	Desc	Date	Monthly Plan			Daily actual			Hour worked			Monthly Act.					Total Ops	Semi Direct	Added Value Target	Good/NG
2				#REF!	1st.	2nd.	3rd.	1st.	2nd.	3rd.	1st.	2nd.	3rd.	1st.	2nd.	3rd.	HC	Build Days
3	FEC 2	BAY 1	985B/241B/881A/051A LENS-BEZEL ASSY	#REF!	3.0			2	1		8	4		2.0	0.5	0.0	2.5	1.0	2.50	4.0	########	#REF!
4	FFV 1	BAY 1	010B CHL AFS/HG/SPORT, LH/RH	#REF!	2.0			3			7.5			2.8	0.0	0.0	2.8	1.0	2.8	4.4	########	#REF!
5	FGB 1	BAY 1	985B/241B Base HL	#REF!	6.0			5			8			5.0	0.0	0.0	5.0	1.0	5.0	7.9	########	#REF!
6	FSA FGB 2	BAY 1	985B/241B REFL B SUBASY	#REF!	3.0	1.0		3	1		8	8		3.0	1.0	0.0	4.0	1.0	4.0	6.3	########	#REF!
7
8	FAQ	BAY 2	150A LH FOG	#REF!	2.0			2			8			2.0	0.0	0.0	2.0	1.0	2.0	3.2	########	#REF!
9	FAQ 2	BAY 2	150A RH FOG	#REF!	2.0			2			8			2.0	0.0	0.0	2.0	1.0	2.0	3.2	########	#REF!
10	FBD 2	BAY 2	AUTOMATED PES FL	#REF!				2			4			1.0	0.0	0.0	1.0	1.0	1.0	1.6	$ 616.20	#REF!
11	FFN A	BAY 2	THRA/T6ZA/TGSX FTSL, RH	#REF!										0.0	0.0	0.0	0.0	1.0	0.0	0.0	$ -	#REF!
12	FFN B	BAY 2	THRA/T6ZA/TGSX FTSL, LH	#REF!										0.0	0.0	0.0	0.0	1.0	0.0	0.0	$ -	#REF!
13	FGH 1	BAY 2	P375 HL,LH	#REF!	4.0		3.0	4			9.5			4.8	0.0	0.0	4.8	1.0	4.8	7.5	########	#REF!
14	FGH 2	BAY 2	P375 HL,RH	#REF!	4.0		3.0	4			9.5			4.8	0.0	0.0	4.8	1.0	4.8	7.5	########	#REF!
15	FGQ 1	BAY 2	U540(2)/U540 LENS/BEZEL SUB	#REF!	1.0				1			1.3		0.0	0.2	0.0	0.2	1.0	0.2	0.3	$ 100.13	#REF!
Sheet5

Cell Formulas
Range	Formula
D2	D2	='X:\Plant-Flora\Assembly-Flora\Public\Trish McGrew\Prosser Proj\KPI_Report_Dump.xlsx'!Table01[Prod Date]
B8:B15,B3:B6	B3	=+VLOOKUP($A3,NAL_KPI_Report.xlsm!WC_INFO[#Data],2,FALSE)
C8:C15,C3:C6	C3	=+VLOOKUP($A3,NAL_KPI_Report.xlsm!WC_INFO[#Data],3,FALSE)
D8:D15,D3:D6	D3	=SUMIF('X:\Plant-Flora\Assembly-Flora\Public\Trish McGrew\Prosser Proj\KPI_Report_Dump.xlsx'!Table01[Work Center],A3,'X:\Plant-Flora\Assembly-Flora\Public\Trish McGrew\Prosser Proj\KPI_Report_Dump.xlsx'!Table01[Earned Value])
N8:P15,N3:P6	N3	=SUM(H3*K3)/8
Q8:Q15,Q3:Q6	Q3	=SUM(N3:P3)
R8:R15,R3:R6	R3	=1
S8:S15,S3:S6	S3	=Q3*R3
T8:T15,T3:T6	T3	=+S3*[NAL_KPI_Report.xlsm]WC_INFO!Semi_Direct
U8:U15,U3:U6	U3	=+T3*[NAL_KPI_Report.xlsm]WC_INFO!Added_Value
V8:V15,V3:V6	V3	=D3>=U3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V3:V6,V8:V15	Cell Value	=FALSE	text	NO
V3:V6,V8:V15	Cell Value	=TRUE	text	NO