Sumproduct Not Working In Closed Workbook & shows #N/A

[jasgat]

Hi everyone,

Im new to formulas in excel.
And this is driving me crazy..
I had been search high and low in all forums for 5hrs!!
I tried to input by SUMPRODUCT, but i still cant solve my problem!

PLEASE HELP ME!!! =S

Here's the problem...

I am using this formula in Product Balance Update.xls,Tsuchiura worksheet that update data from A-Tsuchiura.xls, INVOICE worksheet.

When both workbooks are is opened, all linking are fine.

But upon A-Tsuchiura.xls is closed, my linking in Product Balance Update.xls,Tsuchiura worksheet will shows #N/A.

I had tried many ways for sumproduct
1) =SUMPRODUCT(--('\\Som\factory\Distribution\Shipment\[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3),--('\\Som\factory\Distribution\Shipment\[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))

2) =SUMPRODUCT(('\\Som\factory\Distribution\Shipment\[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3)*('\\Som\factory\Distribution\Shipment\[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))

I had read through this forum that sumproduct should work in closed workbook but I still cant get it why this error ocurr..

Please refer to attached to have a clearer picture of error

please help me solve this issue please??

[NBVC]

Are you sure there are no #N/A errors in columns B or G of the INVOICE sheet?

Usually, if a function doesn't work on closed workbooks, you get the #VALUE! error returned not the #N/A error.. so there must be something else not right.

[Richard Buttrey]

Hi, and welcome to the forum,

Please note the advice in the forum rules about pictures. You are more likely to get a response if you attach the actual workbook containing the sheet/area that's giving a problem.

Attaching a workbook with an embedded picture of the problem area doesn't really help others solve your problem.

Kind regards

[jasgat]

Hi all,

Regarding NBVC qns, there areno errors on Invoice sheet.

I had posted the actual workbook instead of pics.

Please help!!

Regards,
Jasmine

[ROBERTGRAHAM01]

I too have had this problem with a sumproduct. I searched everywhere for a solution but I honestly never found one!

It's madness, it should definitely work but it never shows a value if the source data is closed.

Let me know if you have any luck! Sorry I couldn't help

[NBVC]

Well, I am not sure, but I think Excel is confused about the format of Column B in the Invoice Sheet.

I noticed your underlying format is currency...when there are no "numbers" in that column.

What I did was select column B and go to Data|Text to Columns. Then I skipped to the 3rd window and selected Text from the Column Data Format area.

Then click Ok.

If you still see N/A, hit F9 to recalculate...

I hope that works...it did for me.

[oldchippy]

Hi,

How about this in B3

=SUMPRODUCT(--('\\Som\factory\Distribution\Shipment\[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=Tsuchiura!A3),--('\\Som\factory\Distribution\Shipment\[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))

[jasgat]

Hi all,

sorry for the late reply..

I had tried 2 methods.

NBVC : I tried your method. After editing to text format, the whole formula is shown out into text. I also tried F9 but no response..

(please refer to attached)

Oldchippy : i tried urs too. but shows a #name error..

please helP!!

[oldchippy]

OK,

Try this,

=SUMPRODUCT(--('\\Som\factory\Distribution\Shipment\[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A4),--\\Som\factory\Distribution\Shipment\[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97)

Remove the second set of brackets

[Domenic]

Try the following...

1) Select B3 in your workbook called 'Product Balance Update.xls'.

2) Format the cell as 'General'.

3) Re-enter the formula or click into the formula bar and press ENTER.

4) Copy/paste or drag the formula down the column

See the attached files.

Hope this helps!

[jasgat]

So sorry for the late reply.

I tried in the xls file that I gave you all.
It works.

But when I tried in my original copy the problem came back!!

OMG!! HELP!!

Now i send u all the original A-Tsuchiura.xls with the product bal.!!

Please help!!!

https://rcpt.yousendit.com/589599173...c1c380e6db9c3f