Can I use a text input to define another file to reference?

[bob]

This is a pretty complex problem I think.

I would like to be able to link to cells in separate files, and have that
file path determined by a text input.

Example.

I am working in a file called "Test". I want to reference cell A1 in a file
called "Inputs" saved on my desktop. Is there a way to look at a text input
in my "Test" file that would find A1 in "Inputs" if the text reads
"C:\Documents and Settings\Desktop\Inputs.xls"

 

[Thomas [PBD]]

Bob,

There are two options here. Would you like to require the book to be open
in order to pull the value, or would you like the book to be able to pull the
value while it is closed?

 

[Mike H]

Bob,

Maybe this

myfile = Range("A1").Value & ".xls"
mypath = "C:\Documents and Settings\Desktop\"
Workbooks.Open Filename:=mypath & myfile

With your filename in A1

Mike

 

[bob]

Closed. The intent is I have many similar files with data saved in the same
cells in each of those files. I want to be able to select which file to pull
from just by typing the file path/name, without having to manually relink it
each time.

Thanks,
bob

Bob,

There are two options here. Would you like to require the book to be open
in order to pull the value, or would you like the book to be able to pull the
value while it is closed?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

This is a pretty complex problem I think.

I would like to be able to link to cells in separate files, and have that
file path determined by a text input.

Example.

I am working in a file called "Test". I want to reference cell A1 in a file
called "Inputs" saved on my desktop. Is there a way to look at a text input
in my "Test" file that would find A1 in "Inputs" if the text reads
"C:\Documents and Settings\Desktop\Inputs.xls"

 

[bob]

Mike,

Anyway to do it without writing script for a custom formula? The only
reason is that I want to be able to share this with other users.

bob

 

[Thomas [PBD]]

Bob,

The only thing that i have ver found that could accomplish this is within
the Morefunc add-on, with the Indirect.ext function.

Simple steps here for the Morefunc add-in:
http://xcell05.free.fr/morefunc/english/

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

Closed. The intent is I have many similar files with data saved in the same
cells in each of those files. I want to be able to select which file to pull
from just by typing the file path/name, without having to manually relink it
each time.

Thanks,
bob

Bob,

There are two options here. Would you like to require the book to be open
in order to pull the value, or would you like the book to be able to pull the
value while it is closed?

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

This is a pretty complex problem I think.

I would like to be able to link to cells in separate files, and have that
file path determined by a text input.

Example.

I am working in a file called "Test". I want to reference cell A1 in a file
called "Inputs" saved on my desktop. Is there a way to look at a text input
in my "Test" file that would find A1 in "Inputs" if the text reads
"C:\Documents and Settings\Desktop\Inputs.xls"