Excel - How to indirectly access a file whose name is in a cell
 

In workbook A, I want to have a cell filled with the name of a second .xls
file (which may change) and then access cells in workbook B from the original
workbook A.

Excel - How to indirectly access a file whose name is in a cell
 

With the text [MyJunk.xls]Sheet1!$A$1 in A1 of File A, and formula
=INDIRECT(A1) in D2, I can display the content of A1 in the file called
MyJunk.XLS
With the text MyJunk.xls in A2,the text Sheet1!A1 in B2, and the formula
=INDIRECT("["&A2&"]"&B2) in D2 of File A, I can again display a cell from
the second file
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"DaveAlbany" wrote in message
...
In workbook A, I want to have a cell filled with the name of a second .xls
file (which may change) and then access cells in workbook B from the
original
workbook A.

Excel - How to indirectly access a file whose name is in a cell
 

indirectly
right! There's a worksheetfunction INDIRECT that exactly does what you
need, it uses the content of a cell to build a reference. See Excel
help on indirect. I am not sure if it will work with filenames, just
have a try.

arno

Excel - How to indirectly access a file whose name is in a cel
 

Thank you very much...I had actually tried this, but with your advice - I was
able to discover that it didn't work in my workbooks because one of the files
has a '-' in its name and this throws off the reference. When I saved the
file without the '-' in its name, it worked fine.

I'm gonna keep playing, because there must be a way to do it with '-' also.

"Bernard Liengme" wrote:

With the text [MyJunk.xls]Sheet1!$A$1 in A1 of File A, and formula
=INDIRECT(A1) in D2, I can display the content of A1 in the file called
MyJunk.XLS
With the text MyJunk.xls in A2,the text Sheet1!A1 in B2, and the formula
=INDIRECT("["&A2&"]"&B2) in D2 of File A, I can again display a cell from
the second file
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"DaveAlbany" wrote in message
...
In workbook A, I want to have a cell filled with the name of a second .xls
file (which may change) and then access cells in workbook B from the
original
workbook A.

Excel - How to indirectly access a file whose name is in a cel
 

Hi Dave,

you should make a formula to that workbook as a sample and create an
indirect-formula exactly like it, maybe there are eg. some ' -
characters missing in your formula.

arno

Excel - How to indirectly access a file whose name is in a cell
 

With text in A1: My-Junk.xls, text in A2: Sheet1, text in A3: A1
Formula in A4: =INDIRECT(CHAR(39)&"["&A1&"]"&B1&CHAR(39)&"!"&C1)
I can pick up the content of the cell in the other file.
Many thanks to Bob Umlas for showing where I was going wrong.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"DaveAlbany" wrote in message
...
In workbook A, I want to have a cell filled with the name of a second .xls
file (which may change) and then access cells in workbook B from the
original
workbook A.