Indirect and sumif functions with multiple workbooks
 

I have one workbook that basically needs to use a sumif function with the sum
range being an indirect reference to a named range. This works when the
named range is in the same workbook as the sumif formula, but I am having
trouble getting the sumif function to work using an indirect reference to a
named range in a different workbook. I tried the following formula to no
avail to reference the named range, assuming 'store alignment g1' and 'store
alignment g2' refer to the other file's name and tab respecitvely:


=SUMIF('[Total Country Data File (District & Store P&L).xls]IS
Data'!$B$5:$B$40243,F3=SUMIF('[Total Country Data File (District & Store
P&L).xls]IS Data'!$B$5:$B$40243,F3,(INDIRECT(("'["&'Store
Alignment'!G1&".xls]"&'Store Alignment'!G2&"'!"Indirect(h1)))))

Indirect H1 is on the sheet where my sumif formula is, and it is referring
to a named cell range on the other open workbook (total country data file).
Can you nest indirect formulas like this? Is there another way to sum a named
range in another workbook?

Indirect and sumif functions with multiple workbooks
 

I haven't really looked at your formula, but as a quick observation,
Indirect does *not* work on closed WBs.

Open all concerned WBs and see if your formula works.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"acyakos" wrote in message
...
I have one workbook that basically needs to use a sumif function with the
sum
range being an indirect reference to a named range. This works when the
named range is in the same workbook as the sumif formula, but I am having
trouble getting the sumif function to work using an indirect reference to
a
named range in a different workbook. I tried the following formula to no
avail to reference the named range, assuming 'store alignment g1' and
'store
alignment g2' refer to the other file's name and tab respecitvely:


=SUMIF('[Total Country Data File (District & Store P&L).xls]IS
Data'!$B$5:$B$40243,F3=SUMIF('[Total Country Data File (District & Store
P&L).xls]IS Data'!$B$5:$B$40243,F3,(INDIRECT(("'["&'Store
Alignment'!G1&".xls]"&'Store Alignment'!G2&"'!"Indirect(h1)))))

Indirect H1 is on the sheet where my sumif formula is, and it is referring
to a named cell range on the other open workbook (total country data
file).
Can you nest indirect formulas like this? Is there another way to sum a
named
range in another workbook?