Linking with a closed file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the sumif formula to gather info from another wookbook (a data workbook) that is closed. When the data workbook is open, the formulas work fine and pull the info I need...but when the data workbook is closed the formulas just return errors
I would like to be able to use these formulas without having the data workbook file open. Any suggestions?
 
From a previous post:

I think =sumif() dislikes closed files.

But maybe you could rewrite your formula using:
=SUM(IF('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8,
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30))
(all one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(I'd actually create the formula with both workbooks open, so I could just point
and click on the ranges. Then when you close the "file M" workbook, you'll see
the full path to the closed file.)

Another formula that'll work without having to remember to ctrl-shift-enter:

=SUMPRODUCT(--('C:\My Documents\excel\[file m.xls]Sheet1'!$A$1:$A$30=D8),
'C:\My Documents\excel\[file m.xls]Sheet1'!$D$1:$D$30)
(all one cell, again)
 
Joe,

You might want to build links in your workbook to the data you need. Links
are able to read from closed files.

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl

Joe B. said:
I am using the sumif formula to gather info from another wookbook (a data
Click to expand...
workbook) that is closed. When the data workbook is open, the formulas work
fine and pull the info I need...but when the data workbook is closed the
formulas just return errors.
I would like to be able to use these formulas without having the data
Click to expand...
workbook file open. Any suggestions?
 
Back
Top