I'd like to set up a formula that references a filename that is contained
within a cell.

my formula currently is:

=VLOOKUP($B3,[02.10.06.XLS]Summary'!$M$6:$T$27,5,FALSE)))

I would like the filename to read from cell C2, which contains '03.10.06' so
it would read:

=(VLOOKUP($B3,[(contentof c2).XLS]Summary'!$M$6:$T$27,5,FALSE)))

This would just save me so much time finding and replacing dates in the
formula, which I am doing at the moment.

Any ideas?

shakey1181 wrote:
I'd like to set up a formula that references a filename that is
contained within a cell.

my formula currently is:

=VLOOKUP($B3,[02.10.06.XLS]Summary'!$M$6:$T$27,5,FALSE)))

I would like the filename to read from cell C2, which contains
'03.10.06' so it would read:

=(VLOOKUP($B3,[(contentof c2).XLS]Summary'!$M$6:$T$27,5,FALSE)))

This would just save me so much time finding and replacing dates in
the formula, which I am doing at the moment.

Any ideas?


Hi,

you could use the INDIRECT function inside VLOOKUP:

=(VLOOKUP($B3,INDIRECT("["&C2&".XLS]Summary'!$M$6:$T$27"),5,FALSE)))

but pay attention: the file whose name is in C2 *must* be opened, otherwise
the result would be #VALUE!

If you need the file in C2 to be closed you could download a free add-in,
Morefunc from Laurent Longre (http://xcell05.free.fr/english/) which
contains the function INDIRECT.EXT, that is the same as INDIRECT working
also with closed workbooks.


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy