PLEASE HELP - Problem with 2 linked workbooks

Hi

I have linked 2 workbooks and some information from wb1 is sent to wb2, but wb2 is refusing to update unless I have wb1 open - it only returns #VALUE.

When I have set up links previously they have worked fine but this one is baffling me!

Can anyone help please??

Debbie

Hi

My understanding is that use of certain Excel functions can result in a #VALUE error (when linking two workbooks). What functions are you using? Are you linking the workbooks by typing the formula into the cell manually or are you using paste special?

M

Hi Mark

This is an example of my formula:

=SUMIF(filename.xls]MAY'!$O$8:$O$300,41029,'filename.xls]MAY'!$AD$8:$AD$300)

There is one of these for each month (entered manually), then I copied/pasted them and changed the excel date number for others...........so in answer to your question there are both.

Hope this makes sense!

Hi Debbie,

You can not gather information from workbooks which are stored in different folders (left parenthesis, it is possible with VBA). Assuming when you created all your data, the files where in the same folder, followed by moving wb1, wb2, etc. to different folders. Right?

Shall I show you the VBA way (do you know what I am talking about?)?

Hi Debbie

I have found the following. When using your formula I do indeed get the #VALUE error, but I don't when I use another function (just as an example, SUM). I have heard that in circumstances like this
SUMPRODUCT can be used instead of SUMIF. Is this something you would consider?

Mark

PS I think there's a square bracket missing from your formula, but I'm assuming this is just a typo and not in original formula

open the source file first then open the next containing link to the source file.

if it ask to update the link select update link & then select the source file by going in to its location folder.

Hi Debbie

Just tried out using SUMPRODUCT to do the same as your SUMIF was doing and it works fine with no #VALUE error
M

Mark

How do I compose the sumproduct formula, I've never used it before?

Hi Debbie

This worked for me, let me know how you get on:

=SUMPRODUCT(([file1.xls]MAY!$AD$8:$AD$300)*([file1.xls]MAY!$O$8:$O$300=41029))
M

Thanks Mark

You're welcome
M