Creating a formula using a 2nd workbook??

  • Thread starter Thread starter GBL
  • Start date Start date
G

GBL

Hi Guys:
Using the SumIf function in workbook #1, I created a formula that
calculated source data from another workbook (#2) by having both workbooks
open. The calculation was successful (appearing in workbook #1).
I now discover that that the calculated value only appears in the
workbook (#1) when the source-workbook (#2) is open simultaneously;
otherwise the dreaded #VALUE error appears in the cell.
What have I done wrong?

Thank You in Advance!!
 
Perhaps if you copied/pasted your formula here you would get more help. Some
functions, such as indirect, do not work in closed workbooks.
 
Hi Don:
The function that I spoke of is SUMIF which calculates select/chosen
entries in a 2nd workbook. It works fine if workbook #2 is open. I
believe this is refered to as a "link". In troubleshooting, I did an
Edit>Link on workbook #1 and found two references but I simply do not
understand why only the first reference is pulling in the data and the
second reference (the SUMIF cells) are not (unless workbook #2 is open).
I'm new at this and at this stage am guessing, really. Is automatic linking
restricted only to one reference at a time?
Do not understand your comment about "indirect". Here's the formula:
=SUMIF('C:\My
Documents\EXCELxls\[Prescriptions04.xls]Sheet1'!$G$5:$G$31,'C:\My
Documents\EXCELxls\[Prescriptions04.xls]Sheet1'!$A$2,'C:\My
Documents\EXCELxls\[Prescriptions04.xls]Sheet1'!$K$5:$K$31)
 
The worksheet function INDIRECT does NOT work with closed workbooks.
I just tried sumif with a closed workbook and it worked fine.
=SUMIF('C:\yourfolder\[yourfile.xls]yoursheet'!$F$7:$F$20,"Interest",'C:\you
rfolder\[yourfile.xls]yoursheet'!$D$7:$D$20)

--
Don Guillett
SalesAid Software
(e-mail address removed)
GBL said:
Hi Don:
The function that I spoke of is SUMIF which calculates select/chosen
entries in a 2nd workbook. It works fine if workbook #2 is open. I
believe this is refered to as a "link". In troubleshooting, I did an
Edit>Link on workbook #1 and found two references but I simply do not
understand why only the first reference is pulling in the data and the
second reference (the SUMIF cells) are not (unless workbook #2 is open).
I'm new at this and at this stage am guessing, really. Is automatic linking
restricted only to one reference at a time?
Do not understand your comment about "indirect". Here's the formula:
=SUMIF('C:\My
Documents\EXCELxls\[Prescriptions04.xls]Sheet1'!$G$5:$G$31,'C:\My
Documents\EXCELxls\[Prescriptions04.xls]Sheet1'!$A$2,'C:\My
Documents\EXCELxls\[Prescriptions04.xls]Sheet1'!$K$5:$K$31)


Don Guillett said:
Perhaps if you copied/pasted your formula here you would get more help. Some
functions, such as indirect, do not work in closed workbooks.
Click to expand...
Click to expand...
 
Back
Top