Indirect in combination with ROW
 

If created a formula a while ago in which I refer to sheets with names
like 1 and 2 -- ROW($1:$2) this way I get a correct amount from the
sumproduct formula.

=SUMPRODUCT(SUMIF(INDIRECT("'" & ROW($1:$2) & "'!A2:Z1000"),
$A4,INDIRECT("'" & ROW($1:$2) & "'!B2:B1000")))

I want to rename the sheets to something which makes more sense but if
I try that (like for instance to sheet1) my formula returns an error?

Thanks in advance,

Mike

Indirect in combination with ROW
 

"Nickneem" wrote...
If created a formula a while ago in which I refer to sheets with names
like 1 and 2 -- ROW($1:$2) this way I get a correct amount from the
sumproduct formula.

=SUMPRODUCT(SUMIF(INDIRECT("'" & ROW($1:$2) & "'!A2:Z1000"),
$A4,INDIRECT("'" & ROW($1:$2) & "'!B2:B1000")))

I want to rename the sheets to something which makes more sense but if
I try that (like for instance to sheet1) my formula returns an error?

Your original formula worked because ROW($1:$2) returned the actual
worksheet names as an array, {1;2}. If you want to change your worksheet
names, you'd need to change the formula so that instead of ROW($1:$2) it
uses something else that returns an array of the worksheet names. Simplest
would be to enter the new worksheet names in a range, e.g., X99:x100, then
refer to that range instead.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$X$99:$X$100&"'!A2: Z1000"),
$A4,INDIRECT("'"&$X$99:$X$100&"'!B2:B1000")))

Indirect in combination with ROW
 

Thanks Harlan, so if I understand it correct I can't have my sheet
named like "Inv Japan", "Inv UK", "Inv USA", etc?
I really have to dig deeper into this sumproduct / indirect thing, I've
set it up once but now it looks all abracadabra to me after a couple of
months.

Thanks for your help!

Michael

Indirect in combination with ROW
 

"Nickneem" wrote...
Thanks Harlan, so if I understand it correct I can't have my sheet
named like "Inv Japan", "Inv UK", "Inv USA", etc?
I really have to dig deeper into this sumproduct / indirect thing, I've
set it up once but now it looks all abracadabra to me after a couple of
months.

No. You can name your worksheets anything you want, but unless you name them
as whole numbers between 1 and 65536 you can't use ROW to generate an array
of worksheet names.

If you enter the following into X99:X102,

Inv Japan
Inv UK
Inv USA
Inv Mars Colony

and these are names of worksheets with identical layouts, then you can use
the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&$X$99:$X$102&"'!A2: Z1000"),
$A4,INDIRECT("'"&$X$99:$X$102&"'!B2:B1000")))

to sum the entries in all of these worksheets' B2:B1000 ranges where the
corresponding cell in column A of the respective worksheets matches the
value of cell A4 in the worksheet containing this formula.

So you just need to replace the ROW(..) call with something that evaluates
to an array of the names of the worksheets over which you want to sum
conditionally.

Indirect in combination with ROW
 

Thanks a million Harlan, works perfectly!

Michael