Including a named range as a cell reference within a nested SUMPRODUCT, SUMIF, INDIRECT Function

Question

I am using these nested formulas to sum data from several sheets.

This formula works perfectly:

=SUMPRODUCT(SUMIF(INDIRECT("'"&_Pub&"'!A:A"),$A9,INDIRECT("'"&_Pub&"'!"&AE$6)))

_Pub is a workbook level dynamic named range of a list of several sheets.  I would like to replace _Pub with a cell reference (AE5) and include the named range _Pub in cell AE5.  I have multiple summary and detail sheets within a given workbook and the named range is different for each summary sheet.  It is easier to copy and paste the formula if I could use a cell reference of the named range rather than hardcoding the named range in the formula.  However when I change the formula to this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&AE$5&"'!A:A"),$A9,INDIRECT("'"&_Pub&"'!"&AE$6)))

The result is #REF

 

Any ideas?

Answer 1

INDIRECT will not work with a dynamic named range. You could define a new name using EVALUATE(somecell) where somecell is the cell that contains "_Pub" (without quotes) and then use that name in the formula. You could also use worksheet level names instead and point them to the relevant ranges.

Answer 2

Hi,

Try this (untested)

=SUMPRODUCT(SUMIF(INDIRECT("'"&INDIRECT(AE$5)&"'!A:A"),$A9,INDIRECT("'"&_Pub&"'!"&AE$6)))

Answer 3

Thank you unfortunately this didn't work.

Answer 4

Thank you.  I couldn't quite understand your suggestion and have gone a different route instead.