SUMIF function linking several worksheets

[mgonzales]

I have a workbook with multiple tabs. The last is a worksheet that link
information from all of the others, a total page. Is there any way t
insert new worksheets that will be linked to the total page withou
having to add to the formulas each time? For example, when using
simple sum function on a single worksheet if any row is inserted withi
the formula range the formula automatically adjusts to include the ne
row. Is this possible when adding new worksheets? This is the formul
that I have currently written:
=SUMIF('sheet1"!B:B,N8,'Sheet1"!C:C)+SUMIF('sheet2"!B:B,N8,'Sheet2"!C:C)

Thanks in advance for any help

 

[Frank Kabel]

Hi
one workaround:
- create a list of your sheet names you want to include in your
formula. e.g. put these names in cells A1:Ax on a separate sheet and
define a name for this range (with 'Insert - Name - Define. e.g. name
this range wslist).
Now use the following formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"" & wslist &"'!B:B"),N8,INDIRECT("'" &
wslist &"'!C:C")))

now you only have to add a name to your list of sheets and change the
name definition. Or also possible define the name wslist with the
following formula
=OFFSET($A$1,0,0,COUNTA($A$1:$A$100))
this will change the name definition automatically

 

[mgonzales]

Frank,
I wrote the formula as you suggested.

=SUMPRODUCT(SUMIF(INDIRECT("'" & wslist &"'!B:B"),N8,INDIRECT("'"
wslist &"'!C:C)))

Now I get the error message #NUM!, any suggestions?

Thank

 

[Frank Kabel]

Hi
have you defined the name 'wslist'. Also try chaning the formula to
=SUMPRODUCT(SUMIF(INDIRECT("'" & wslist &"'!B1:B1000"),N8,INDIRECT("'"
&wslist &"'!C1:C1000)))

 

[mgonzales]

Thank you, thank you, thank you. It worked. I was defining my list a
the entire column not just the ones with text in them