Using an Excel spreadsheet, I want

[raa44634]

Using an Excel spreadsheet, I want to create a summary worksheet that pulls in the value of the same cells but from various excel files. The various files pulled are named using the same path and convention EXCEPT for the true file name (that is based on let's say a market or region.)

Let's have a list of those regions running down column A of the current worksheet.

Is there a way in Excel to write the forumla such as:
B2 = '...\Folder1\Subfolder1\Subfolder2\REFERENCE VALUE OF CURRENT SHEET CELL A2 FOR THE FILE NAME\...

This way I can use on formula across the board and not have to deal with Find and Replace for the file names?
Thanks!

 

[PeterMoran]

Hi raa44634,

You can do what you want by using the INDIRECT function along the following lines:

B2 = '...\Folder1\Subfolder1\Subfolder2\REFERENCE VALUE OF CURRENT SHEET CELL A2 FOR THE FILE NAME\...

would be B2:
=INDIRECT("'[...\Folder1\Subfolder1\Subfolder2\"&A2&"]SheetName'!A2&quot

Here is a working example I used recently, where the filesname was in cell V2:

U2: =INDIRECT("'["&V2&"]SOW Register'!$A$5:$D$200&quot

The double quotes make up the string, while the single quotes are needed where there might be a space in the path, filename or sheetname.

Good Luck!

Peter Moran
Two heads are always better than one

 

[raa44634]

Works well up to a point. When the summary file opens and asks to "Update Links?" the cells using the INDIRECT error out with #REF.

However, if I open the files from which the summary is referencing, then the summary cells populate.

What gives? Is this a shortcoming or an error on my part? Thx!

- Tony -

 

[PeterMoran]

Hi Tony,

With INDIRECT you will get a #REF rather than an Update Links request. I suspect that your Update Links request is not generated by the code associated with these links, but maybe other external links.

I addressed this problem in two ways:
1. The actual code includes an IF ISERROR condition to cover the link not being open, and in this situation provides a message that the user can understand:

=IF(B2<>&quot;&quot;,IF(ISERROR(VLOOKUP(T2,INDIRECT(&quot;'[&quot;&V$2&&quot;]SOW Register'!$A$5:$D$200&quot,4,FALSE)),&quot;*** SOW NOT FOUND ***&quot;,VLOOKUP(T2,INDIRECT(&quot;'[&quot;&V$2&&quot;]SOW Register'!$A$5:$D$200&quot,4,FALSE)),&quot;&quot

2. I also included code when the workbook is opened to request that the user open the required files to ensure all the links are satisfied. If the instructions are followed then there is no problem with the links.

The end result will work, and with INDIRECT there is the flexibility which I think you were looking for.

Good Luck

Peter Moran
Two heads are always better than one!