Linking to Excel file - filename specified indirectly

[Pedro-G]

I don't have the English version of Excel, so some English terms I use may be unusual or incorrect. Please bear with me.


I'd like to make refrenses (or links) and lookups to other Excel files, but I need to define the actual filename in another cell in the referensing spreadsheet.

I try to clarify with a couple of very simple examples. Say I have two Excel files, Master.xls and Slave.xls. Normally in the Master.xls I would make a link to cell C4 in Slave.xls as follows:

='C:\Foldername\[Slave.xls]Sheetname'!$C$4

That works. However, instead of writing the [Slave.xls] in the formula itself, I would like it to look up the filename from another cell in the Master.xls.

I know the following doesn't work, but it maybe gives a picture of what I'm asking for:

='C:\Foldername\[[A2].xls]Sheetname'!$C$4

...and A2 would contain the text "Slave". Is there a way to format it so that it works? Couldn't find it from the helpfiles or searching this site (and others).

Also acceptable (but not preferred) would be that the cell A2 contains the whole filename including the .xls extension, such as "Slave.xls" instead of just "Slave".

 

[Aladin Akyurek]

Re: Linking to Excel file - filename specified "indirec

I don't have the English version of Excel, so some English terms I use may be unusual or incorrect. Please bear with me.


I'd like to make refrenses (or links) and lookups to other Excel files, but I need to define the actual filename in another cell in the referensing spreadsheet.

I try to clarify with a couple of very simple examples. Say I have two Excel files, Master.xls and Slave.xls. Normally in the Master.xls I would make a link to cell C4 in Slave.xls as follows:

='C:\Foldername\[Slave.xls]Sheetname'!$C$4

That works. However, instead of writing the [Slave.xls] in the formula itself, I would like it to look up the filename from another cell in the Master.xls.

I know the following doesn't work, but it maybe gives a picture of what I'm asking for:

='C:\Foldername\[[A2].xls]Sheetname'!$C$4

...and A2 would contain the text "Slave". Is there a way to format it so that it works? Couldn't find it from the helpfiles or searching this site (and others).

Also acceptable (but not preferred) would be that the cell A2 contains the whole filename including the .xls extension, such as "Slave.xls" instead of just "Slave".

Download and install the morefunc.xll add-in in order to work with closed files...

=INDIRECT.EXT("'C:\Foldername\["&A2&"]Sheetname'!C4")

 

[Pedro-G]

Re: Linking to Excel file - filename specified "indirec

Download and install the morefunc.xll add-in in order to work with closed files...

=INDIRECT.EXT("'C:\Foldername\["&A2&"]Sheetname'!C4")

Thanks for the answer.

However, I need this at work and I don't have the admin rights for WindowsXP to install it. Of course I could ask to have it installed on my computer, but hundreds of other people at my company will use my spreadsheet and would therefore need to install the morefun.xll. It's just not possible to install add-ins for all of them.

Any other suggestions?

 

[Aladin Akyurek]

Re: Linking to Excel file - filename specified "indirec

Download and install the morefunc.xll add-in in order to work with closed files...

=INDIRECT.EXT("'C:\Foldername\["&A2&"]Sheetname'!C4")

Thanks for the answer.

However, I need this at work and I don't have the admin rights for WindowsXP to install it. Of course I could ask to have it installed on my computer, but hundreds of other people at my company will use my spreadsheet and would therefore need to install the morefun.xll. It's just not possible to install add-ins for all of them.

Any other suggestions?

Morefunc allows itself to be included in the workbook that you want to distribute.

If the target file is open at any time it's needed, you can replace INDIRECT.EXT with INDIRECT.

 

[Pedro-G]

Re: Linking to Excel file - filename specified "indirec

Morefunc allows itself to be included in the workbook that you want to distribute.

If the target file is open at any time it's needed, you can replace INDIRECT.EXT with INDIRECT.

Okay, I tried with Morefunc and it seems to work! For references to specified cells, anyway - like I had in my example. I still have some rough edges, but I hope I'll sort them out.

Now, how to do the same using the VLookup function? I mean, search in another closed file, and specify the filename in another cell.

I tried using VLookup and entering ["&A2&"] instead of the filename. The filename Slave.xls was in the cell A2. Excel popped up a browser screen for me to select which file I mean, it didn't look it up from cell A2. After that, it worked for that one session, but when I close and open the Master.xls file, the link dosn't work any more. If I manually update the link, every time it pops me the browser window to point out the file again. It doesn't even matter if I type ["&garbage&"] instead of ["&A2&"] in the VLOOKUP funtion; Excel doesn't even try to use the A2 cell for seeing the filename.

Any ideas?

 

[Aladin Akyurek]

Re: Linking to Excel file - filename specified "indirec

Morefunc allows itself to be included in the workbook that you want to distribute.

If the target file is open at any time it's needed, you can replace INDIRECT.EXT with INDIRECT.

Okay, I tried with Morefunc and it seems to work! For references to specified cells, anyway - like I had in my example. I still have some rough edges, but I hope I'll sort them out.

Now, how to do the same using the VLookup function? I mean, search in another closed file, and specify the filename in another cell.

I tried using VLookup and entering ["&A2&"] instead of the filename. The filename Slave.xls was in the cell A2. Excel popped up a browser screen for me to select which file I mean, it didn't look it up from cell A2. After that, it worked for that one session, but when I close and open the Master.xls file, the link dosn't work any more. If I manually update the link, every time it pops me the browser window to point out the file again. It doesn't even matter if I type ["&garbage&"] instead of ["&A2&"] in the VLOOKUP funtion; Excel doesn't even try to use the A2 cell for seeing the filename.

Any ideas?

=VLOOKUP(LookupValue,INDIRECT.EXT(...),ReturnColumn,MatchType)

You need to give a table range to INDIRECT.EXT.

 

[Pedro-G]

Re: Linking to Excel file - filename specified "indirec

=VLOOKUP(LookupValue,INDIRECT.EXT(...),ReturnColumn,MatchType)

You need to give a table range to INDIRECT.EXT.

Thanks a million for your answers so far. However, I didn't quite understand this one. I tried to read up on this, but couldn't figure it out how to enter the name of the external file in another cell, where the filename can be changed by just changing the contents of that cell.

All the closed external files into which I'll be doing the VLOOKUP are three letters long, such as HJU.xls, PLY.xls and so forth. Let's say that:

- LookupValue is entered in cell A1
- Table Range in the external file is K1:L10
- External filename is entered in cell A2
- ReturnColum is 2
- MatchType is FALSE

Could you please give me the exact formula, using that info? With the correct quotes, semicolons and stuff. I'm sorry to be such a pain, but I've been busting my brain, I just get errors.

 

[Pedro-G]

Re: Linking to Excel file - filename specified "indirec

All the closed external files into which I'll be doing the VLOOKUP are three letters long, such as HJU.xls, PLY.xls and so forth. Let's say that:

- LookupValue is entered in cell A1
- Table Range in the external file is K1:L10
- External filename is entered in cell A2
- ReturnColum is 2
- MatchType is FALSE

Ok, finally figured it out myself. Turns out that I had to learn to use the &'s and "'s the hard way. In case someone else ever fumbles with the same question, here is the formula using the above mentioned data, plus the fact that the external files are located at C:\Datafiles\ :

=VLOOKUP(A1;INDIRECT.EXT("'C:\Datafiles\["&A2&".xls]Sheetname'!$K$1:$L$10");2;FALSE)