SUMIF linking to closed workbooks.

G

Guest

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
 
G

Guest

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.
 
B

Bob Phillips

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)
 
D

Dave Peterson

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.
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
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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.
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Alternative to SUMif when linking to closed workbooks 8
Linking Workbooks with formulas Sumif 3
Linking to other workbooks question 4
Sumif using Vlookup to compare a separate list 4
how to reference different workbooks/sheets in formula? 1
Linking Workbooks using file names in a range 2
Get two Values from closed Workbooks 2
xlfTextRef from another workbook 1

Top