Referencing external workbook

[Capitano]

Dear All,

Trying to select a range of data from a number of workbooks

have created a name that returns the right text to reference this data if it were typed in:

Cell I4 denotes Level Directory
Cell L4 denotes Excel workbook
Cell J5 denotes the Sheet in the workbook


Name Col1:
="'c:\my documents\maths program\level "&Master!$I$4&"\[book "&Master!$L$4&".xls]Sheet"&Master!$J$5&"'!a"&ROW()-2

Any ideas gretfully recieved!

Tim,
Manchester England

 

[just_jon]

Dear All,

Trying to select a range of data from a number of workbooks

have created a name that returns the right text to reference this data if it were typed in:

Cell I4 denotes Level Directory
Cell L4 denotes Excel workbook
Cell J5 denotes the Sheet in the workbook


Name Col1:
="'c:\my documents\maths program\level "&Master!$I$4&"\[book "&Master!$L$4&".xls]Sheet"&Master!$J$5&"'!a"&ROW()-2

Any ideas gretfully recieved!

Tim,
Manchester England

You had the right idea; but to get it to work, you need the INDIRECT function

=INDIRECT("'c:\my documents\maths program\level " & Master!$I$4 & "\[book " & Master!$L$4& ".xls]Sheet" & Master!$J$5 &"'!a"& ROW()-2)

(*untested* but I think I've got the syntax right; if not post back.)

 

[Ekim]

Note that the INDIRECT function only works when the referenced workbook (WB) is opened.

The following works with a closed WB.

=VLOOKUP(B18,'C:\My Documents\Temp\Demo.xls'!MyRange,2,FALSE)

where:
B18 in the open WB holds the item to lookup in the closed WB.
Demo.xls is the closed WB and is located at C:\My Documents\Temp\
MyRange refers to $A$1:$B$10 in Demo.xls.

Or, you can reference the range directly:

=VLOOKUP(B18,'C:\My Documents\Temp\[Demo.xls]Sheet1'!$A$1:$B$10,2,FALSE).

Alternative formula (also works with a closed WB):

=INDEX('C:\MyDocuments\Temp\Demo.xls'!MyRange,MATCH(B18,'C:\Documents\Temp\Demo.xls'!Rng,0),2)

Where the named range “Rng” refers to A1:A10 in Demo.xls.


HTH


Mike

 

[Capitano]

Thanks Very Much

Tim

 

[ecybert]

Note that the INDIRECT function only works when the referenced workbook (WB) is opened.

The following works with a closed WB.

=VLOOKUP(B18,'C:\My Documents\Temp\Demo.xls'!MyRange,2,FALSE)

where:
B18 in the open WB holds the item to lookup in the closed WB.
Demo.xls is the closed WB and is located at C:\My Documents\Temp\
MyRange refers to $A$1:$B$10 in Demo.xls.

Or, you can reference the range directly:

=VLOOKUP(B18,'C:\My Documents\Temp\[Demo.xls]Sheet1'!$A$1:$B$10,2,FALSE).

Alternative formula (also works with a closed WB):

=INDEX('C:\MyDocuments\Temp\Demo.xls'!MyRange,MATCH(B18,'C:\Documents\Temp\Demo.xls'!Rng,0),2)

Where the named range “Rng” refers to A1:A10 in Demo.xls.


HTH


Mike

Hi Mike,
Thnx for the tips. Isn't it possible to use variables (eg cell references) for the file locations within these formulae though?
Thnx in advance for the response(s).