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??
Last edited by VBA Noob; 05-27-2008 at 02:25 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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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
Hi all,
Regarding NBVC qns, there areno errors on Invoice sheet.
I had posted the actual workbook instead of pics.
Please help!!
Regards,
Jasmine
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
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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))
oldchippy
-------------
If you are happy with the help you have received, please click the blue scales icon in the blue bar of the post.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
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!!
Last edited by jasgat; 06-08-2008 at 10:18 PM.
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
oldchippy
-------------
If you are happy with the help you have received, please click the blue scales icon in the blue bar of the post.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
Hi oldchippy,
I tried, but cant.. still #N/A.
Does this help?
http://office.microsoft.com/en-us/ex...039361033.aspx
oldchippy
-------------
If you are happy with the help you have received, please click the blue scales icon in the blue bar of the post.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
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..
I think I've run out of suggestions?
oldchippy
-------------
If you are happy with the help you have received, please click the blue scales icon in the blue bar of the post.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
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!
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks