 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2006Excel - 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. 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  SignatureBernard 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. 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 [quoted text clipped - 8 lines] > > original > > workbook A. 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? SignatureBernard 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 [quoted text clipped - 19 lines] >> > original >> > workbook A. 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 >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 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. SignatureBernard 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.