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

**jasgat** · 05-27-2008, 02:21 AM

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** · 05-27-2008, 09:00 AM

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** · 05-27-2008, 09:20 AM

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** · 05-29-2008, 03:30 AM

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** · 05-29-2008, 05:45 AM

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** · 05-29-2008, 08:01 AM

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** · 05-29-2008, 08:17 AM

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** · 06-08-2008, 10:16 PM

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** · 06-09-2008, 06:06 AM

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

**jasgat** · 06-11-2008, 12:42 AM

Hi oldchippy,

I tried, but cant.. still #N/A.

**oldchippy** · 06-11-2008, 03:51 AM

Does this help?

http://office.microsoft.com/en-us/ex...039361033.aspx

**jasgat** · 06-12-2008, 01:31 AM

Old Chippy : Cant.. no use to it.. The formula can work when the original workbook is opened. But when it is close it will show #N/A..

**oldchippy** · 06-12-2008, 05:29 AM

I think I've run out of suggestions?

**Domenic** · 06-12-2008, 11:25 AM

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** · 07-15-2008, 10:17 PM

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