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

[alexfortin]

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

 

[Aladin Akyurek]

A formula (alone) is never a good substitute for a problem description... Try to at least describe what E13, B10, and B13 house and which of these cells are the "user inputs"?

 

[alexfortin]

Good point...

Cell E13 is the actual SKU - it is an input but not changed by the user every time

Cell B10 and B13 are the start and end weeks, these are the main variables the user changes

 

[Aladin Akyurek]

Good point...

Cell E13 is the actual SKU - it is an input but not changed by the user every time

Cell B10 and B13 are the start and end weeks, these are the main variables the user changes

Control+shift+enter, not just enter:

=IFERROR(SUM(IF('[Estimate IBP 2011 Plan RTEC.xlsx]->Ship $'!$A$4:$A$173=$E13,IF('[Estimate IBP 2011 Plan RTEC.xlsx]->Ship $'!$B$3:$BC$3>=$B$10,IF('[Estimate IBP 2011 Plan RTEC.xlsx]->Ship $'!$B$3:$BC$3<=$B$13,'[Estimate IBP 2011 Plan RTEC.xlsx]->Ship $'!$B$4:$BC$173)))),0)

 

[alexfortin]

That is brilliant. I understand it works as an array somehow but I have not spent much time with array's. Could you please help me understand how it works? Thanks again, great and short formula!