Closed workbook link by formula

[Dkline]

I have created a formula which goes and gets data from a variety of closed
workbooks which have many worksheets. What I want to do is create a formula
to calculate the link to the closed workbook for a specified cell.

This is the working formula
='C:\Documents and Settings\dkline\My
Documents\NFP\[ah_0902.xls]ah_0902'!A5


So I carved that up into four pieces.
Path is in Cell A6:
C:\Documents and Settings\dkline\My Documents\NFP\

Workbook name is in Cell A7:
ah_0902.xls

Worksheet name is in Cell A8:
ah_0902
Cell reference is in Cell A9:
A5

In cell A10 I assemble the formula:
="'"&A6&"["&A7&"]"&A8&"'!"&A9

which produces
'C:\Documents and Settings\dkline\My
Documents\NFP\[ah_0902.xls]ah_0902'!A5

When I try and use an Indirect is returns a #REF. Help tells me
Indirect only works with open workbooks.

Is there a way I can use the calculated formula to get the data from a
closed workbook?

Can VLOOKUP do it and how?

 

[Dkline]

Thnaks for the tip. It's working as advertised.

Take a look at MVP John Walkenbach's technique for
retrieving a value from a closed file:

http://j-walk.com/ss/excel/tips/tip82.htm

HTH
Jason
Atlanta, GA

-----Original Message-----
I have created a formula which goes and gets data from a variety of closed
workbooks which have many worksheets. What I want to do is create a formula
to calculate the link to the closed workbook for a specified cell.

This is the working formula
='C:\Documents and Settings\dkline\My
Documents\NFP\[ah_0902.xls]ah_0902'!A5


So I carved that up into four pieces.
Path is in Cell A6:
C:\Documents and Settings\dkline\My Documents\NFP\

Workbook name is in Cell A7:
ah_0902.xls

Worksheet name is in Cell A8:
ah_0902
Cell reference is in Cell A9:
A5

In cell A10 I assemble the formula:
="'"&A6&"["&A7&"]"&A8&"'!"&A9

which produces
'C:\Documents and Settings\dkline\My
Documents\NFP\[ah_0902.xls]ah_0902'!A5

When I try and use an Indirect is returns a #REF. Help tells me
Indirect only works with open workbooks.

Is there a way I can use the calculated formula to get the data from a
closed workbook?

Can VLOOKUP do it and how?






.

 

[bmg9611]

Is there not a way to use this as a User Defined Function?

 

[Harlan Grove]

Is there not a way to use this as a User Defined Function?

See

http://www.google.com/[email protected]