Sum Variable Ranges based on user input - Data in closed workbooks

Ladies and Gents,



I need some help figuring out how I can sum a range of cells closed workbooks based on information inputted in an open book.



Essentially I am looking up data in tables in the closed workbooks, the data can be index/matched fairly easily however I am needing to sum 50 or more cells based on what the user inputs.



The inputs are time (weeks - going across) and SKU - (going down) the actual data is sales information. What the end result becomes is for product X during weeks 1-20 it sold Y amount of product



Currently I am using this formula but I found that when the workbook is closed it no longer works



=IFERROR(SUM(OFFSET(INDEX('[Estimate IBP 2011 Plan RTEC.xls]->Ship $'!$B$4:$BC$173,MATCH($E13,'[Estimate IBP 2011 Plan RTEC.xls]->Ship $'!$A$4:$A$173,0),MATCH($B$10,'[Estimate IBP 2011 Plan RTEC.xls]->Ship $'!$B$3:$BC$3,0)),0,0,1,$B$13)),0)




I guess Offsets don't work in closed books. I know there is an answer to this but I can't seem to figure it out...I appreciate your help

I am sorry, I didn't realize they were the same forum.