Indirect & Sumproduct on closed workbook

[leighmetcalfe]

I currently have a work book that contains production data to work out per day what total hours each process has planned.

I have created a pull sheet that uses indirect and sumproduct as per below.

=SUMPRODUCT(INDIRECT(Parameters!$I$76)*(INDIRECT(Parameters!$I$73)=M$2)*(INDIRECT(Parameters!$I$74)=$G9)

Parameters!$I$76 = the range that contains the hours

Parameters!$I$73 = the process

Parameters!$I$74 = the date

All the above works perfectly but now I have 4 sheets within the workbook.

I want to move these sheets to a new book, so other uses can view whilst I maintain the master workbook.

I have tried to move to a new book but all the value come up as #ref even when the work book is open.

I'm struggling as I cant see whats the issue is.

 

[Aladin Akyurek]

INDIRECT does not work with closed books. Try either Harlan Grove's PULL or Laurent Longrè's INDIRECT.EXT (from the morefunc.xll add-in).

 

[leighmetcalfe]

I have tired the PULL function but that only works to pull the value of a cell, I cant get it to work with SUMPRODUCT.

The INDIRECT.EXT would not be an option as our IT department would not install it to our work PC's