Named range in array formula?

Originally Posted by samplecode

Hi, hair pulling time. I have an excel array formula which references a number of ranges on a different worksheet (within the same workbook - see formula below). It works fine for explict cell ranges (e.g. 'MyDataSheet'!K13:K300) but will not work for dynamic named ranges defined by an offset formula (=OFFSET('MyDataSheet'!$K$13,0,0,COUNTA('MyDataSheet'!$K13:$K300),1)).

Does anyone know if there is an issue with using dynamic named ranges in array formulae on different worksheets within the same workbook?

{=SUM(IF('PGLSE Turnover'!K13:K300=$B5,IF(RIGHT('PGLSE Turnover'!G13:G300,2)=F$2,'PGLSE Turnover'!I13:I300,0),0))*$C$5} (array formula entered with ctrl-shft-enter)

Hi,


=SUM(IF(Sheet1!K13:K30=$B5,IF(RIGHT(Sheet1!G13:G30,2)=F$2,Sheet1!I13:I30,0),0))*$C$5

(a mod of your line) works


'MyDataSheet'!K13:K300) but will not work for dynamic named ranges defined by an offset formula (=OFFSET('MyDataSheet'!$K$13,0,0,COUNTA('MyDataSheet'!$K13:$K300),1)).

'MyDataSheet'!K13:K300) << is a defined range, and can be used in permitted formula,
=OFFSET('MyDataSheet'!$K$13,0,0,COUNTA('MyDataSheet'!$K13:$K300),1) << is also a (possible) range, but the range needs to be calculated and the formula need to be one that would permit such an array entry, ie

=VLOOKUP("AA",OFFSET(Sheet1!$K$13,0,0,COUNTA(Sheet1!$K13:$K300),1),1,FALSE)

works.

does that answer your question?

---


hth
---