Indirect and sumif functions with multiple workbooks

A

acyakos

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?
 
R

RagDyer

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top