Links #REF!

[Suzanne-Fusion]

I've been working in excel for many years and use linking frequently.
Recently, it appears that unless all linked files are OPEN I get a #REF!
symbol. Once I open the source file, the numbers appear... this does NOT
happen for EVERY link... on some and one have any one aspect that's similar.

In order for me to see all numbers I'm finding I have to have 10-12
different files open... help!!!!

 

[Dave F]

Some functions do not work if they reference closed, external workbooks.
Best practice is to avoid linking formulas to external workbooks.

If you provide an example of the formulas that are returning the #REF error
perhaps someone can offer an alternative function which works with closed
external workbooks.

Dave

 

[Suzanne-Fusion]

Dave:
Me again...

here's the formula:
=GETPIVOTDATA("Paid Amount",'M:\User\SJM\EXCEL\All Cash Flow\Check Registers
2007\[2007 Checks Written.xls]Dept Proj by
Month'!$A$3,"Dept","Prod","Month",DATE(2007,1,1))

a bit indepth but I've verified once the "2007 checks written.xls" is open,
the number is correct...

The ONLY thing I can think of is that the result is coming from a pivot
table... but when I refresh the pivot table and numbers change, the result
changes correctly... but ONLY when the file is open.
thanks

 

[Dave F]

GETPIVOTDATA is one of the functions that doesn't work when it references an
external workbook. Put your pivot table and the GETPIVOTDATA function in the
same work book and you will solve your problem.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

Dave:
Me again...

here's the formula:
=GETPIVOTDATA("Paid Amount",'M:\User\SJM\EXCEL\All Cash Flow\Check Registers
2007\[2007 Checks Written.xls]Dept Proj by
Month'!$A$3,"Dept","Prod","Month",DATE(2007,1,1))

a bit indepth but I've verified once the "2007 checks written.xls" is open,
the number is correct...

The ONLY thing I can think of is that the result is coming from a pivot
table... but when I refresh the pivot table and numbers change, the result
changes correctly... but ONLY when the file is open.
thanks

Some functions do not work if they reference closed, external workbooks.
Best practice is to avoid linking formulas to external workbooks.

If you provide an example of the formulas that are returning the #REF error
perhaps someone can offer an alternative function which works with closed
external workbooks.

Dave

 

[Suzanne-Fusion]

Dave:
While your answer isn't what I wanted to hear, at least now I know!!! Guess
I gotta figure out how to formulate external figures into something other
than pivot tables. Ya think may MS could tweak the functionality to make this
work???

You've been very helpful.
thanks a lot
suzanne

GETPIVOTDATA is one of the functions that doesn't work when it references an
external workbook. Put your pivot table and the GETPIVOTDATA function in the
same work book and you will solve your problem.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

Dave:
Me again...

here's the formula:
=GETPIVOTDATA("Paid Amount",'M:\User\SJM\EXCEL\All Cash Flow\Check Registers
2007\[2007 Checks Written.xls]Dept Proj by
Month'!$A$3,"Dept","Prod","Month",DATE(2007,1,1))

a bit indepth but I've verified once the "2007 checks written.xls" is open,
the number is correct...

The ONLY thing I can think of is that the result is coming from a pivot
table... but when I refresh the pivot table and numbers change, the result
changes correctly... but ONLY when the file is open.
thanks

Some functions do not work if they reference closed, external workbooks.
Best practice is to avoid linking formulas to external workbooks.

If you provide an example of the formulas that are returning the #REF error
perhaps someone can offer an alternative function which works with closed
external workbooks.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


:

I've been working in excel for many years and use linking frequently.
Recently, it appears that unless all linked files are OPEN I get a #REF!
symbol. Once I open the source file, the numbers appear... this does NOT
happen for EVERY link... on some and one have any one aspect that's similar.

In order for me to see all numbers I'm finding I have to have 10-12
different files open... help!!!!