Reply to thread

Message: [QUOTE="Harlan Grove, post: 3715228"] .... But the 'dly wrksht' worksheet is in a different, apparently closed workbook. Excel returns #REF! for names defined as formulas in closed workbooks. Note the OP's specs: "this is the formula for each name... ADMDAY=OFFSET('dly wrksht'!$B$2,1,1,COUNTA('dly wrksht'!$B:$B),1) and PHYNO=OFFSET('dly wrksht'!$D$2,1,3,COUNTA('dly wrksht'!$D:$D),4)" [single quotes added] There's no robust way to do this unless the two COUNTA calls return the same result. Otherwise, the two derived ranges could have different numbers of rows, and the different 5th args to OFFSET ensure they have different numbers of columns. I'll assume the two COUNTA calls return the same result. In the workbook that needs to refer to this other workbook define the names ADMDAY: ='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$C$3:$C$1002 PHYNO: ='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$G$3:$J$1002 N: =COUNTA('H:\ExcelDocs\[SheetA.xls]dly wrksht'!$B:$B) S: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1000,1)) and use a formula like =SUMPRODUCT((PHYNO=B3)*(ADMDAY=D2)*(S<=N)) Adjust the bottom row index as needed, but don't use more rows than needed. References to large ranges in other, closed workbooks can really slow things down and eat memory. [/QUOTE]