| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking Files that are closed | Excel Discussion (Misc queries) | |||
| Copying From Closed Workbooks | Excel Worksheet Functions | |||
| Information from Closed workbooks | Excel Discussion (Misc queries) | |||
| Linking to a closed workbook | Excel Worksheet Functions | |||
| linking problem with closed documents | Excel Worksheet Functions | |||
Linear Mode
