Ps. It was an excellent tip from Lori Miller.

Dave Peterson wrote:

Just a note about changing those strings to formulas.

If the sending workbook/worksheet doesn't exist, then after the edit|replace,
you'll see a prompt for every non-existent file. The only way out of this is to
dismiss each of those dialogs or to kill excel (through the task manager).

Instead of using that edit|replace, you (or the OP or me!) can use data|text to
columns. This seems to plop all the formulas back into the range (single column
at a time, though) and instead of the prompt that needs to be dismissed, you'll
see a #ref error.

I learned this within the last week from another poster and it worked in my
simple tests in xl2003.

It may come in handy for you, too, Harlan.

Harlan Grove wrote:

Dave Peterson wrote...
Both =sumif() and =indirect() are non-starters. They will each
fail if the sending workbook is closed.

Yup.

You'll have to try to get the indirect.ext() function to work for
you and you'll have to use a different function than =sumif()--
maybe =sumproduct() or the array formula sum(if()).

There's another alternative that may be better if the values in the
OP's source workbook don't change (presumably the case since the OP is
accessing a closed workbook).

Starting with the OP's pseudoformula

=SUMIF(
'c:\My Documents\[$A$1 C$2.xls]Costing summary'!$A$28:$A$847,
$A5,
'c:\My Documents\[$A$1 C$2.xls]Costing summary'!$C$28:$C$847
)

Looks like this formula would be filled right and down from cell C5.
If so, enter the following formula in cell C5.

C5:
="=SUMPRODUCT(--('C:\My documents\[" & $A$1 & C$2 &
".xls]Costing summary'!$A$28:$A$847="& $A5 & "),
'C:\My Documents\[" & & $A$1 & C$2 &
".xls]Costing summary'!$C$28:$C$847)"

This is a string-valued formula that will look like a formula. Copy it
and paste into the cells that should have similar formulas, e.g.,
C5:J24. Select the entire range of these formulas, copy, and paste
special as values. This will convert the string-valued formulas into
constant text strings - not formulas. With the range still selected,
run the Edit Replace command, replacing = with =. This may appear to
be a do-nothing operation, but it effectively enters each of these
cells as formulas. If A1 contained foo, C2 contained bar and A5
contained "xyz" (INCLUDING the double quotes), the resulting formula
in cell C5 would be

C5:
=SUMPRODUCT(--('C:\My documents\[foobar.xls]Costing summary'!$A$28:$A
$847="xyz"),'C:\My Documents\[foobar.xls]Costing summary'!$C$28:$C$847)

--

Dave Peterson

--

Dave Peterson