Maybe you can let excel do the work.
Edit|links|change source.
Be aware that the "sending" workbook has to be open for =Sumif() to work.
And if you decide to use =indirect(), then the other workbook(s) have to be
open, too.
I put the file name of the open workbook in A1 and then used this formula:
=SUMIF(INDIRECT("'["&A1&"]Sheet1'!$E:$E"),B21,
INDIRECT("'["&A1&"]Sheet1'!$aa:$aa"))
A1 contained this:
1 Feb 06 - 30 Apr 06.xls
Nothing else.
Remember that the sending workbook has to be open for =sumif() to work and for
=indirect() to work--a double whammy!
=sumif() has an equivalent =sumproduct() expression (not using the whole column.
and
Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
That includes =indirect.ext() that may help you.
(I've never used it, though.)
You may be able to use a combination of both these to keep the sending workbook
closed.
wrote:
I'm trying to improve the following line of code so that I can
substitute different file names when necessary, but can't get the
syntax right with the Indirect function.
=SUMIF('[1 Feb 06 - 30 Apr 06.xls]Sheet1'!$E:$E,B21,'[1 Feb 06 - 30 Apr
06.xls]Sheet1'!$AA:$AA)
The spreadsheet that this line is in runs to roughly 100 lines, and a
similar formula is used in two other cell locations on each row. The
spreadsheet that it references (1 Feb 06 - 30 Apr 06.xls in this
instance) runs to roughly 13,500 lines.
If I use a different spreadsheet (e.g. 1 May 06 - 31 Jul 06.xls), then
it means changing the above line roughly 300 times for 600 occurences.
What I have been trying to do insert '1 May 06 - 31 Jul 06.xls' into
one cell and then use the indirect function within the sumif. In this
way I would only have to make one single change to the entire
spreadsheet.
Any help would be appreciated.
--
Dave Peterson