SUMIF linking to closed workbooks.
 

My colleague is having trouble linking to another workbook, which he wants to
remain closed. The link looks like this:

=-SUMIF('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL - Northampton
J16 M1.xls]Array List'!$B$3:$B$26,"=01/08/2007",'G:\Master Appraisals &
Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)

Trouble is, we have around 16 workbooks we're linking in this way, so
amalgamating is not an option.

Can this work with workbooks when they remain closed?
Any help appreciated. Thanks, Greg

SUMIF linking to closed workbooks.
 

sumif will not work with closed workbooks,
a workaround is to use a worksheet with diect references to each significant
cell in the closed workbooks and do your sumif on this worksheet.
You can protect the sheet so that others can't see it easily if you don't
wnat them to see the data.

"foofightin" wrote:

My colleague is having trouble linking to another workbook, which he wants to
remain closed. The link looks like this:

=-SUMIF('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL - Northampton
J16 M1.xls]Array List'!$B$3:$B$26,"=01/08/2007",'G:\Master Appraisals &
Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)

Trouble is, we have around 16 workbooks we're linking in this way, so
amalgamating is not an option.

Can this work with workbooks when they remain closed?
Any help appreciated. Thanks, Greg

SUMIF linking to closed workbooks.
 

Use SUMPRODUCT

=-SUMPRODUCT('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL -
Northampton J16 M1.xls]Array List'!$B$3:$B$26=--"2007-08-01"), 'G:\Master
Appraisals & Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"foofightin" wrote in message
...
My colleague is having trouble linking to another workbook, which he wants
to
remain closed. The link looks like this:

=-SUMIF('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL -
Northampton
J16 M1.xls]Array List'!$B$3:$B$26,"=01/08/2007",'G:\Master Appraisals &
Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)

Trouble is, we have around 16 workbooks we're linking in this way, so
amalgamating is not an option.

Can this work with workbooks when they remain closed?
Any help appreciated. Thanks, Greg

SUMIF linking to closed workbooks.
 

I think Bob had a typo in his formula:

=-SUMproduct(--('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL -
Northampton
J16 M1.xls]Array List'!$B$3:$B$26=date(2007,1,8)),'G:\Master Appraisals &
Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array List'!$C$3:$C$26)

And the other change is that I like to refer to dates using =date(2007,1,8) and
Bob uses --"2007-08-01".

I used January 8, 2007 and Bob used August 1, 2007.

foofightin wrote:

My colleague is having trouble linking to another workbook, which he wants to
remain closed. The link looks like this:

=-SUMIF('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL - Northampton
J16 M1.xls]Array List'!$B$3:$B$26,"=01/08/2007",'G:\Master Appraisals &
Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)

Trouble is, we have around 16 workbooks we're linking in this way, so
amalgamating is not an option.

Can this work with workbooks when they remain closed?
Any help appreciated. Thanks, Greg

--

Dave Peterson

SUMIF linking to closed workbooks.
 

Thanks for your replies:-

Bj, we want to avioid this, as we have literally thousands of cells to link.

Bob, unfortunately the formula does not work. For a start, there was a
messing ( at the start of the 2nd array. When i make this change it then
corrects itself with a * in between the arrays. When all this is done, the
cell no longer displays a #value, but a blank field, which we know to be
incorrect.

Are there any alternaitves?
Many Thanks, Greg

"Bob Phillips" wrote:

Use SUMPRODUCT

=-SUMPRODUCT('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL -
Northampton J16 M1.xls]Array List'!$B$3:$B$26=--"2007-08-01"), 'G:\Master
Appraisals & Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"foofightin" wrote in message
...
My colleague is having trouble linking to another workbook, which he wants
to
remain closed. The link looks like this:

=-SUMIF('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL -
Northampton
J16 M1.xls]Array List'!$B$3:$B$26,"=01/08/2007",'G:\Master Appraisals &
Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)

Trouble is, we have around 16 workbooks we're linking in this way, so
amalgamating is not an option.

Can this work with workbooks when they remain closed?
Any help appreciated. Thanks, Greg

SUMIF linking to closed workbooks.
 

Try this

=-SUMPRODUCT(--('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL -
Northampton J16 M1.xls]Array List'!$B$3:$B$26=--"2007-08-01"), 'G:\Master
Appraisals & Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"foofightin" wrote in message
...
Thanks for your replies:-

Bj, we want to avioid this, as we have literally thousands of cells to
link.

Bob, unfortunately the formula does not work. For a start, there was a
messing ( at the start of the 2nd array. When i make this change it then
corrects itself with a * in between the arrays. When all this is done, the
cell no longer displays a #value, but a blank field, which we know to be
incorrect.

Are there any alternaitves?
Many Thanks, Greg

"Bob Phillips" wrote:

Use SUMPRODUCT

=-SUMPRODUCT('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL -
Northampton J16 M1.xls]Array List'!$B$3:$B$26=--"2007-08-01"), 'G:\Master
Appraisals & Cashflows\BLEL proformas\[BLEL - Northampton J16
M1.xls]Array
List'!$C$3:$C$26)



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"foofightin" wrote in message
...
My colleague is having trouble linking to another workbook, which he
wants
to
remain closed. The link looks like this:

=-SUMIF('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL -
Northampton
J16 M1.xls]Array List'!$B$3:$B$26,"=01/08/2007",'G:\Master Appraisals &
Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)

Trouble is, we have around 16 workbooks we're linking in this way, so
amalgamating is not an option.

Can this work with workbooks when they remain closed?
Any help appreciated. Thanks, Greg

SUMIF linking to closed workbooks.
 

Thanks for the replies everyone, problem now seems to be resolved.
Combination of ironing out the wrinlkes with the formula, and making sure the
date is formatted correctly.

Greg

"Dave Peterson" wrote:

I think Bob had a typo in his formula:

=-SUMproduct(--('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL -
Northampton
J16 M1.xls]Array List'!$B$3:$B$26=date(2007,1,8)),'G:\Master Appraisals &
Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array List'!$C$3:$C$26)

And the other change is that I like to refer to dates using =date(2007,1,8) and
Bob uses --"2007-08-01".

I used January 8, 2007 and Bob used August 1, 2007.

foofightin wrote:

My colleague is having trouble linking to another workbook, which he wants to
remain closed. The link looks like this:

=-SUMIF('G:\Master Appraisals & Cashflows\BLEL proformas\[BLEL - Northampton
J16 M1.xls]Array List'!$B$3:$B$26,"=01/08/2007",'G:\Master Appraisals &
Cashflows\BLEL proformas\[BLEL - Northampton J16 M1.xls]Array
List'!$C$3:$C$26)

Trouble is, we have around 16 workbooks we're linking in this way, so
amalgamating is not an option.

Can this work with workbooks when they remain closed?
Any help appreciated. Thanks, Greg

--

Dave Peterson