Indirect and Sumif Syntax Problems

[[email protected]]

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.

[email protected] 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

[[email protected]]

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.
>
> [email protected] 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]

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

[email protected] 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.
> >
> > [email protected] 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