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.

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

Dave

You're a genius. That will save me hours of work.

I spent about two hours yesterday trying different ways of writing that
formula, but just couldn't get it sorted.

Thanks very much for the help.


Dave Peterson wrote:

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

Sometimes, it's just setting up a "normal" formula and doing trial and error to
get the =indirect() to match that formula.

(Well, for me, it's trial and error!)

wrote:

Dave

You're a genius. That will save me hours of work.

I spent about two hours yesterday trying different ways of writing that
formula, but just couldn't get it sorted.

Thanks very much for the help.

Dave Peterson wrote:

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

--

Dave Peterson