Dynamic External Workbook Formula

[Co-op Bank]

I have the following formula, its referencing an external workbook:-
=VLOOKUP(C3,'051206Cost Centre 1 .xls'!$D:$F,3,FALSE), the problem is the
prefix to the external workbook is a date and this changes every day (i.e.
the '051206' bit of the formula is the current day in yymmdd format).

Is there any way I type in a single formula that will dynamically refer to
the current days spreadsheet? I have tried the following but it returns an
error: - =VLOOKUP(C3,"'"&TEXT(TODAY(),"yymmdd")&"Cost Centre 1
..xls'!"&$D:$F,3,FALSE)

Any suggestions much appreciated.

Thanks

Brian
Co-op Bank
Manchester, England

[Roger Govier]

Hi Brian

Try
=VLOOKUP(C3,INDIRECT("'"&TEXT(TODAY(),"yymmdd")&"Cost Centre
1.xls'!"&$D:$F),3,FALSE)

Regards

Roger Govier


Co-op Bank wrote:
> I have the following formula, its referencing an external workbook:-
> =VLOOKUP(C3,'051206Cost Centre 1 .xls'!$D:$F,3,FALSE), the problem is the
> prefix to the external workbook is a date and this changes every day (i.e.
> the '051206' bit of the formula is the current day in yymmdd format).
>
> Is there any way I type in a single formula that will dynamically refer to
> the current days spreadsheet? I have tried the following but it returns an
> error: - =VLOOKUP(C3,"'"&TEXT(TODAY(),"yymmdd")&"Cost Centre 1
> .xls'!"&$D:$F,3,FALSE)
>
> Any suggestions much appreciated.
>
> Thanks
>
> Brian
> Co-op Bank
> Manchester, England

[John Michl]

I believe you can use the INDIRECT function to piece together the text
for the reference. I haven't done it recently so don't recall the
exact usage but it might be something like:

=VLOOKUP(C3,INDIRECT"["&TEXT(TODAY(),"yymmdd")&"Cost Centre
1.xls]Sheet1!$D:$F),3,FALSE)

- John