The issue is that if the file where the sumif is pulling
from is closed and I say filter the other file, i get the
#VALUE error. I was told that the sumif function has to
have the file open to see the data. I though it was an
issue with permissions or something. Which is true?
What you were told. But why you would trust me any more than whoever
gave you the correct explanation in the first place? Why don't you
just create a simple test and see for yourself?
Open a new workbook (Book1). In the same instance of Excel, open a
second new workbook (Book2). In Book2, enter the formula
=SUMIF([book1.xls]Sheet1!$A$1:$A$20,1). Save and close both
workbooks, ideally first Book2, then Book1.
Now open just Book2. If you get some warnings, just click OK. You
will see that the SUMIF formula returns a #VALUE error. Obviously,
that is not a permissions error because you created and saved both
files.
Now open Book1 in the same instance of Excel. Look at the SUMIF
formula. It might __still__ returns a #VALUE error(!). In that case,
select the SUMIF cell, press F2, then Enter to "re-enter" the SUMIF
formula. It should now return a valid number, probably zero if you
followed my instructions exactly.
A key point is to open both workbooks in the __same__ instance of
Excel. Normally that happens automagically when you click on the file
icons. But if you changed the default configuration, that might open
each file in separate instances of Excel. It is difficult to tell the
difference visually, unless you are careful.
To be sure, click File > Open in Excel instead of clicking the file
icons.
