Dynamic reference to another workbook

[Saibot]

Hi,

I am registering data in a weekly workbook (A) and treating these data in another weekly workbook (B). In this process I would like the workbook name in the formula for retrieving data in workbook (B) to update automatically with week number from an input cell.

Specifically I have the following CSE formula for retrieving data in (B) from (A):

=SUM(VLOOKUP(B14;'[OEE loggskjema_Finstykk_ 2011 week1.xlsx]Mandag'!$B:$BJ;{2;3;4;5;6;7;8;9;10;11};FALSE))

In this formula only the number after "week" changes. Is there a way to link this to a cell so I can enter the week number and the formula automatically updates?

Any help greatly appreciated!

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use INDIRECT, but the workbook must be open. If the week number is in A1 on the sheet containing the formula try:

=SUM(VLOOKUP(B14;INDIRECT("'[OEE loggskjema_Finstykk_ 2011 week"&A1&".xlsx]Mandag'!$B:$BJ");{2;3;4;5;6;7;8;9;10;11};FALSE))

 

[Saibot]

Thank you Andrew!

The formula you posted worked nicely. However, is there a way to do this without having the workbook that contains the input data open?

Thanks

 

[Andrew Poulsom]

INDIRECT requires that the workbook is open. There is an INDIRECT.EXT function in the MoreFunc Add-In:

http://xcell05.free.fr/morefunc/english/