Alternative to Indirect Function

[Jeff Coleman]

Hello List,
Is there another method to reference a cell in an external
workbook other than the function indirect? The workbooks
that I need to reference will reside on a server and will
be closed when a refresh is made to the consolidating
workbook.

Assume the following:
* Book1.xls is located at: \\Server\ProductType\
* Book1 Contains Sheet named Profile
* Sheet Profile has a cell $A$1="Needed Output"

Book2.xls is a consolidating workbook that references many
external workbooks.
* Book2 Contains a sheet named "Setup"
* In Setup three columns exist where the user types the
path of a specified workbook (\\server\productType\), the
file name (book1.xls) and the name of the profile sheet
(Profile). A fourth column concantenates the column and
places them int the appropriate form (lets' call it Cell
D2 which equals '\\Server\ProductType\[Book1.xls]Profile'!)
* Cell b1 of Sheet2 needs to reference the path from the
Setup sheet and reference cell $A$1 (of book1)

I've tried =Profile!D2 & $A$1 but it doesn't work..
Basically I need the above to be interpretted as
='\\Server\ProductType\[Book1.xls]Profile'!$A$1


Any Suggestions would be greatly appreciated.
Jeff

 

[Vasco]

I think there is no solution using only worksheet
functions.
The problem is that you need to work references as text
and the only way i know to achieve it is through the
INDIRECT. Curious to see other posts.

Vasco

 

[jaf]

Hi Jeff,
Indirect is the only worksheet solution other than a hard coded Excel link.
(='\\Server\ProductType\[Book1.xls]Profile'!$A$1)

Since your user is inputting the path, you could use VBA to get the path
from the user with a userform or inputbox.
Construct the hard coded Excel link and paste it into the cell as a formula,
all with VBA.