SUMPRODUCT(SUMIF(INDIRECT with Dynamic Worksheet Range

[decipher]

Hi,

I am trying to improve a formula so it doesn't need to be edited every time a new worksheet is added. Ive searched around but the examples listed dont seem to fit my current predicament.

The current formula looks like the below (An example of one cell);
=SUMPRODUCT(SUMIF(INDIRECT($M$4:$M$11&"!A:A"),$B6,INDIRECT($M$4:$M$11&"!N:N")))

With the range M4 to M11 containing all the worksheet names.

However I wanted to substitute M11 for something else such as "$M"&$M$2 with cell M2 containing a simple formula (=COUNTA(M4:M50)+3) which will work out how many tabs are in use. This way when they add a worksheet they simply add it to the list and the formula would automatically include the new worksheet without manually amending the $M$11 in the formula (Done via Find and Replace because its used multiple times)

However I cant seem to get anything to work. I have tried embedding a second indirect, using concatenate and just amending the original formula but all end with the #Ref error. Anyone know a solution that could help me out?

Some examples Ive tried unsuccessfully

=SUMPRODUCT(SUMIF(INDIRECT("$M$4:$M$"&$M$2&"!A:A"),$B7,INDIRECT("$M$4:$M$"&$M$2&"!N:N")))
=SUMPRODUCT(SUMIF(INDIRECT($M$4&":$M$"&$M$2&"!A:A"),$B7,INDIRECT($M$4&":$M$"&$M$2&"!N:N")))

I kind of understand why it doesn't work, it needs a link to the worksheet name however by breaking apart the cell its losing that direct link.

Cheers,

[Jacc]

My first choice would be using an Excel Table where I fill in the sheet names. The Table feature will provide you with a dynamic named range and then you use this name in the formula.
http://www.jkp-ads.com/articles/Excel2007tables.asp

My second option would be a dynamic named range.
http://www.contextures.com/xlNames01.html#Dynamic

[decipher]

Mint,

Dynamic Named Range with offset worked a charm!

Thanks for your assistance.

[Jacc]

Glad to hear it works!