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)