Closed workbook link by formula

  • Thread starter Thread starter Dkline
  • Start date Start date
D

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?
 
Thnaks for the tip. It's working as advertised.

Jason Morin said:
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?






.
Click to expand...
Click to expand...
 
Back
Top