File name as parameter

[Ricardo Caicedo]

Hello all

I need to compose a file name from different cells and use that info in order to extract data from the name file that matches that name. Just in excel not with a VBA.

So let me explain with a short example: I have in the cell C9 a text "2013-14" (it will replace xxxx-xx),
cell D9 a text "13-14" (it will replace yy- yy),
cell C10 a Text "Jan" (it will replace ZZZ),and finally
cell D10 the cell that need to be referenced as D24 (it will replace A##).

The file name where I need to get the info have this format:

'S:\GasInc\Services\MKTG\CMS\COM&IND\Pricing\xxxx-xx\[yy-yy Forward and Index Pricing.xlsx]ZZZ 14'!$A##

my excel spreadsheet has to take all this info and just bring what is in the file and cell D24, so the reference should be:

'S:\GasInc\Services\MKTG\CMS\COM&IND\Pricing\2013-14\[13-14 Forward and Index Pricing.xlsx]Jan 14'!$D24


It looks so simple but I tried concatenate and use indirect formula, but is not working....any ideas please

 

[Andrew Poulsom]

You can't use concatenation to create a formula and INDIRECT won't work with a closed workbook, sorry.

 

[Ricardo Caicedo]

No options then...just with VBA....