|
Dynamic External Workbook reference
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 |
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 |
Dynamic External Workbook reference
Hi Brian
I'm sorry I wasn't thinking clearly when I posted you a solution yesterday. In a cell on your Sheet, let's say A1 enter =TEXT(TODAY(),"yymmdd")&"[Cost Centre 1.xls]Sheet1" This assumes the data for the lookup table is on Sheet1, amend accordingly. Then change your formula to =VLOOKUP(C3,INDIRECT(" ' "&A1&" '!$D:$F"),3,FALSE) I have deliberately spaced out the " ' " to show the single quote enclosed between the double quotes. 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 |
Dynamic External Workbook reference
Excellent Thank you very much, you have saved a lot of time
Brian "Roger Govier" wrote: Hi Brian I'm sorry I wasn't thinking clearly when I posted you a solution yesterday. In a cell on your Sheet, let's say A1 enter =TEXT(TODAY(),"yymmdd")&"[Cost Centre 1.xls]Sheet1" This assumes the data for the lookup table is on Sheet1, amend accordingly. Then change your formula to =VLOOKUP(C3,INDIRECT(" ' "&A1&" '!$D:$F"),3,FALSE) I have deliberately spaced out the " ' " to show the single quote enclosed between the double quotes. 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 |
Dynamic External Workbook reference
Hy everybody! I have a problem with Excel. I created a ComboBox with some name. Then I copied this another workbook. When I choose a name in the ComboBox this name isn't change in another workbook. What's the correct form for this? Please help me. Thanks Balázs from Hungary -- ballabali ------------------------------------------------------------------------ ballabali's Profile: http://www.excelforum.com/member.php...o&userid=29663 View this thread: http://www.excelforum.com/showthread...hreadid=491383 |
Dynamic External Workbook reference
|
Dynamic External Workbook reference
You're very welcome Brian, glad it worked for you.
Thanks for the feedback Regards Roger Govier Co-op Bank wrote: Excellent Thank you very much, you have saved a lot of time Brian "Roger Govier" wrote: Hi Brian I'm sorry I wasn't thinking clearly when I posted you a solution yesterday. In a cell on your Sheet, let's say A1 enter =TEXT(TODAY(),"yymmdd")&"[Cost Centre 1.xls]Sheet1" This assumes the data for the lookup table is on Sheet1, amend accordingly. Then change your formula to =VLOOKUP(C3,INDIRECT(" ' "&A1&" '!$D:$F"),3,FALSE) I have deliberately spaced out the " ' " to show the single quote enclosed between the double quotes. 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 |
| All times are GMT +1. The time now is 06:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com