Indirect and Sumif Syntax Problems

[seanswilson]

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]

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.

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.

 

[seanswilson]

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.

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.

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!)

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.

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.

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.