Cacatenation and SUMPRODUCT

[zach]

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?

 

[Bob Phillips]

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

--
---
HTH

Bob

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

 

[zach]

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

--

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

 

[pinmaster]

Hi

Try this:

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

note that the workbook will need to be open.

HTH
Jean-Guy

 

[pinmaster]

Hi,

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

Jean-Guy

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

--

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