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.
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.
