Link in formula to another workbook

E

EstherJ

I have a sumif formula which is looking at another work book. The formulas
work when I have both files open. But if I open the destination file only,
the values are there, then the prompt window appears to update. Whether I
choose to update or not the values change to #REF! and will only reappear
when I open the source file.

Thank you in advance,

Esther
 
R

RagDyeR

The SUMIF() function, as you now know, doesn't work on closed WBs.

However, SUM() and IF() *DO Work*, so you can combine them in an *array*
formula to calculate open and closed WBs.

For example, for use in the same WB:

=SUMIF(D1:D10,"green",E1:E10)

Can be revised to this *array* formula:

=SUM(IF(D1:D10="green",E1:E10))
Must be entered with (CSE), <Ctrl> <Shift> <Enter>

So, what you can do is start the formula in the destination WB:
=SUM(IF(
Navigate to the source WB and click and drag on your range,
Click in the formula bar and complete the formula, and then hit <CSE>.

This automatically returns you to the destination WB, where you'll see that
the formula now contains the path to the source WB.
When you close the source WB, you'll see the formula enlarge even more, as
it now contains the *full* path to the source.

It might look something like this:
{=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green",'C:\New
Project\[school 5.XLS]Sheet1'!$E$1:$E$10))}
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I have a sumif formula which is looking at another work book. The formulas
work when I have both files open. But if I open the destination file only,
the values are there, then the prompt window appears to update. Whether I
choose to update or not the values change to #REF! and will only reappear
when I open the source file.

Thank you in advance,

Esther
 
D

Dave Peterson

And =sumproduct() will work with closed workbooks, too:

{=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green",
'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))}

=sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"),
'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10)


I like not having to ctrl-shift-enter this version.


The SUMIF() function, as you now know, doesn't work on closed WBs.

However, SUM() and IF() *DO Work*, so you can combine them in an *array*
formula to calculate open and closed WBs.

For example, for use in the same WB:

=SUMIF(D1:D10,"green",E1:E10)

Can be revised to this *array* formula:

=SUM(IF(D1:D10="green",E1:E10))
Must be entered with (CSE), <Ctrl> <Shift> <Enter>

So, what you can do is start the formula in the destination WB:
=SUM(IF(
Navigate to the source WB and click and drag on your range,
Click in the formula bar and complete the formula, and then hit <CSE>.

This automatically returns you to the destination WB, where you'll see that
the formula now contains the path to the source WB.
When you close the source WB, you'll see the formula enlarge even more, as
it now contains the *full* path to the source.

It might look something like this:
{=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green",'C:\New
Project\[school 5.XLS]Sheet1'!$E$1:$E$10))}
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have a sumif formula which is looking at another work book. The formulas
work when I have both files open. But if I open the destination file only,
the values are there, then the prompt window appears to update. Whether I
choose to update or not the values change to #REF! and will only reappear
when I open the source file.

Thank you in advance,

Esther
Click to expand...
 
Top