Linking to External Workbook Error - Indirect Alternative?

[alohadboy]

I have a workbook (A) that contains multiple tabs, where each needs to be linked to an external workbook (B) that also contains the division revenue on multiple tabs (Dallas, Houston, etc).

In workbook A, the user will select a division (Dallas for example), which will look up the corresponding revenue in workbook B, on the Dallas tab.

I need to be able to replicate the tab in workbook A such that the user can select a different division for each tab, and it will pull over their corresponding revenue from workbook B.

An indirect formula works, but obviously only if the source workbook is open (B).

Any thoughts on an alternative? This is what I have so far:

=SUMPRODUCT(1*(INDIRECT($B$1&"C4:O4")=E$2)*(INDIRECT($B$1&"A7:A290")=$B29),(INDIRECT($B$1&"C7:O290")))
Where:
B1 is the file reference
E2 is January
B29 is "Revenue"

I appreciate your input.

[dflak]

Could you provide a couple of workbooks with representative, but non-sensitive data? There might be a VBA solution to this.