Dynamic External Workbook reference
Unfortunately, the text(today()), with default windows settings, will get you
something like 05/12/06, not 051206. You could use
RIGHT(YEAR(NOW()),2)&MONTH(NOW())&DAY(NOW())
which will return 05127 for today, which is Dec 7, 2005. If that isn't
good enough you will have use
RIGHT(YEAR(NOW()),2)&IF(MONTH(NOW())<10,"0"&MONTH( NOW()),MONTH(NOW())&IF(DAY(NOW())<10,"0"&DAY(NOW() ),DAY(NOW())))
which will give you 051207. A little complicated.
Hope that helps
"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
|