OK, so this one is pretty complicated.

I have multiple big workbooks with multiple tabs on each. One tab is
the "Summary" tab and basically just has a list of all the tabs.

So let's say it looks like:

Column A Column B

Workbook 1.xls Foo

Column 1 is my workbook name. Column 2 is the name of a sheet in that
workbook.

I'm trying to set up a SumProduct that I can copy down the list (it'll
be in Coumn C) that currently reads:

=SUMPRODUCT((INDIRECT("'[Workbook1.xls]Foo'!$L$64:$L
$500")<0)*(L64:L500<"TOTAL")*(I64:K500))

However, like I said, I want a forumula I can copy down. I tried:

=SUMPRODUCT((INDIRECT("'[&"A1"&]&"B1"&'!$L$64:$L
$500")<0)*(L64:L500<"TOTAL")*(I64:K500))

And several other permutations on that, but nothing seems to work. I
can't understand why Excel isn't accepting the cacatenation.

Any ideas?

=SUMPRODUCT((INDIRECT("'["&A1&"]"&B1&"'!$L$64:$L$500")<0)*(L64:L500<"TOTAL")*(I6 4:K500))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ups.com...
OK, so this one is pretty complicated.

I have multiple big workbooks with multiple tabs on each. One tab is
the "Summary" tab and basically just has a list of all the tabs.

So let's say it looks like:

Column A Column B

Workbook 1.xls Foo

Column 1 is my workbook name. Column 2 is the name of a sheet in that
workbook.

I'm trying to set up a SumProduct that I can copy down the list (it'll
be in Coumn C) that currently reads:

=SUMPRODUCT((INDIRECT("'[Workbook1.xls]Foo'!$L$64:$L
$500")<0)*(L64:L500<"TOTAL")*(I64:K500))

However, like I said, I want a forumula I can copy down. I tried:

=SUMPRODUCT((INDIRECT("'[&"A1"&]&"B1"&'!$L$64:$L
$500")<0)*(L64:L500<"TOTAL")*(I64:K500))

And several other permutations on that, but nothing seems to work. I
can't understand why Excel isn't accepting the cacatenation.

Any ideas?

On Mar 8, 3:00 pm, "Bob Phillips" wrote:
=SUMPRODUCT((INDIRECT("'["&A1&"]"&B1&"'!$L$64:$L$500")<0)*(L64:L500<"TOTAL")*(I6 4:K500))

--
---
HTH

Bob

Nope, I tried that. Doesn't work. Thanks anyway.

Hi,

What does "not work" mean? What kind of result are you getting? Are the
workbooks opened?

Jean-Guy

" wrote:

On Mar 8, 3:00 pm, "Bob Phillips" wrote:
=SUMPRODUCT((INDIRECT("'["&A1&"]"&B1&"'!$L$64:$L$500")<0)*(L64:L500<"TOTAL")*(I6 4:K500))

--
---
HTH

Bob

Nope, I tried that. Doesn't work. Thanks anyway.

Hi

Try this:

=SUMPRODUCT((INDIRECT("'["&A1&"]"&B1&"'!$L$64:$L$500")<0)*(L64:L500<"TOTAL")*(I6 4:K500))

note that the workbook will need to be open.

HTH
Jean-Guy

" wrote:

OK, so this one is pretty complicated.

I have multiple big workbooks with multiple tabs on each. One tab is
the "Summary" tab and basically just has a list of all the tabs.

So let's say it looks like:

Column A Column B

Workbook 1.xls Foo

Column 1 is my workbook name. Column 2 is the name of a sheet in that
workbook.

I'm trying to set up a SumProduct that I can copy down the list (it'll
be in Coumn C) that currently reads:

=SUMPRODUCT((INDIRECT("'[Workbook1.xls]Foo'!$L$64:$L
$500")<0)*(L64:L500<"TOTAL")*(I64:K500))

However, like I said, I want a forumula I can copy down. I tried:

=SUMPRODUCT((INDIRECT("'[&"A1"&]&"B1"&'!$L$64:$L
$500")<0)*(L64:L500<"TOTAL")*(I64:K500))

And several other permutations on that, but nothing seems to work. I
can't understand why Excel isn't accepting the cacatenation.

Any ideas?