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

[DaveAlbany]

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.

 

[Bernard Liengme]

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

 

[arno]

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

 

[DaveAlbany]

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.

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

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.

 

[Bernard Liengme]

If I have a file call MyJunk open and in Book1 I type = and then click on A1
in MYJunk then Book1's formula is =[MyJunk.xls]Sheet1!$A$1
But if the file is called My-Junk then the point and click method gives
='[My-Junk.xls]Sheet1'!$A$1.
Note the single quotes hold [WorksheetName]SheetName
So you need to add the singe quotes within the INDIRECT
None of my (limited) experiments worked. I even tried CODE(39) within the
INDIRECT to add the quote but no luck.
We need a real guru - Chip, Jon, Harman, Peo, Deb (and other I do not want
to annoy!) where are you?

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

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.

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

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.

 

[arno]

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

 

[Bernard Liengme]

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.