Quote Crazy!! Concatenating filenames and cell values

[fitzt70]

This should be easy but I can't figure it out.

I want to run a formula with that references a cell in a separate file but part of the file name varies depending on input in a cell. For example the following formula works fine:

=IF(VLOOKUP($D5,'\\CONFILESRV\Dept\Construction\Development\Fee Info\Fee Summaries\[9418.xlsx]Fee Summary'!$B$8:$F$78,5,FALSE)<>0,"Done","NO ")

But if I want the file name 9148 to change depending on a value in cell A1 I tried the following and no luck

=IF(VLOOKUP($D5,'\\CONFILESRV\Dept\Construction\Development\Fee Info\Fee Summaries\[&A1&.xlsx]Fee Summary'!$B$8:$F$78,5,FALSE)<>0,"Done","NO ")

I can't figure out how to combine the two strings of text and the cell value. I've tried quotes, double quotes, and concatenate but no luck. Since the folder loation and worksheets will be the same I would like to put those in fixed cell locations too.

Any advice out there?

Thanks. I promise to add to your credentials!

[Pete_UK]

You would normally use INDIRECT to construct a cell reference as a string and then get Excel to recognise it as a cell reference.

However, INDIRECT does not work with closed workbooks, so you won't be able to use it in this instance.

One way around this limitation is to download the free add-in morefunc (do a Google search for sites where you can download it from), which will enable you to use the function INDIRECT.EXT, which does work with closed workbooks.

Hope this helps.

Pete

[fitzt70]

Thanks Pete.

I looked at INDIRECT too, but didn't see that it would help. Now I understand that when I enter the function the Excel Open File dialog box appeared. I'm not sure if I will used an add-in as our IT dept here is very strict on allowing us to load any applications.

Tom