I don't undertand something about Excel...
In file A.xls I have these named ranges:
Visits
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
Orders
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
In file B.xls I have this formula:
=SUM('A.xls'!Visits)
which returns the correct sum. So far so good. HOWEVER, this array formula returns a #VALUE! error:
=SUM(('A.xls'!Visits=10)*'A.xls'!Orders)
The above formula works if I paste it into A.xls, but it won't work from B.xls. Does anyone have an explanation? Does Excel not support external named ranges in array formulas?
Thanks,
Tom
In file A.xls I have these named ranges:
Visits
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
Orders
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
In file B.xls I have this formula:
=SUM('A.xls'!Visits)
which returns the correct sum. So far so good. HOWEVER, this array formula returns a #VALUE! error:
=SUM(('A.xls'!Visits=10)*'A.xls'!Orders)
The above formula works if I paste it into A.xls, but it won't work from B.xls. Does anyone have an explanation? Does Excel not support external named ranges in array formulas?
Thanks,
Tom