Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic External Workbook Formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic External Workbook Formula
Hi Brian
Try =VLOOKUP(C3,INDIRECT("'"&TEXT(TODAY(),"yymmdd")&"C ost 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic External Workbook Formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links only update when external workbook is open | Excel Worksheet Functions | |||
Link A Cell In O ne Workbook To A Formula In Another Workbook | Excel Worksheet Functions | |||
how to copy formula from one workbook to another | Excel Discussion (Misc queries) | |||
Excel: Use a name with external workbook reference for data valida | Excel Worksheet Functions | |||
Linking formula to external spreadsheet | Excel Discussion (Misc queries) |