SUMPRODUCT through a network

[Mehhico]

Hello all,

I am trying to use a SUMPRODUCT formula to look into a closed workbook through our server to provide a total number of hours an employee has spent on a particular project.

However, the formula I am using keeps giving me the #VALUE! error.

The formula seems to work when it is referencing cells within the same workbook/sheet, but not through the network to another workbook.

The formula I am using is:

=SUMPRODUCT(--('Z:\_TIMESHEETS\Employee1\2010\[2010 02 08-14_Timesheet_Employee1.xls]Sheet1'!$C$12:$E$69=A55),'Z:\_TIMESHEETS\Employee1\2010\[2010 02 08-14_Timesheet_Employee1.xls]Sheet1'!$L$12:$L$69)

Can someone please review this and provide any assistance.

Thanks again.
Andy

[BlindRI]

First thing you should do is to remove the Z: reference in the formula. The Z: is a mapped drive, most likely only on your machine. This is a very bad way to work through networks. Instead substitute the server name and file path to actually follow as shown.

\\server.name\folder\_TIMESHEETS\Employee1\...

I am not positive it will solve your entire issue, but it will help.

[DonkeyOte]

Andy, the #VALUE! error can always result from underlying errors however...

we know that using ranges of different dimensions with double unary coercion will cause #VALUE! error and that is definitely an issue here, ie:

Sheet1!C12:E69 and Sheet1!L12:L69

One being 3 columns wide the other 1.

To negate that issue we revert to * method from double unary, ie:

Please Login or Register  to view this content.

However, it follows that should any values in summation range be non-numeric this too will generate #VALUE! error given the explicit coercion now taking place against the final range.

Post back if the error value persists - with more info. regards contents of each range.
(a file mock up which demonstrates each range would help - dummy names / values etc but ensure data types are consistent - the fact it's an external file is of no consequence from a functional perspective)

[Mehhico]

Thanks BlindRI and DonkeyOte, I appreciate all of this sort of information.

The mapped network drive is the same on all computers as we constantly reference drawings via autocad X-Refs, but the information about the actual system path is helpful for future work.

The code suggested by DonkeyOte worked perfectly in this situation. Exactly what I needed.

Thanks again!
Andy