Dynamic External Workbook reference

[Guest]

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

 

[Guest]

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

 

[Roger Govier]

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

 

[Guest]

Excellent Thank you very much, you have saved a lot of time
Brian

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

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

 

[ballabali]

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]

My availability (e-mail address removed)

MSN: (e-mail address removed)

Balázs

 

[Roger Govier]

You're very welcome Brian, glad it worked for you.
Thanks for the feedback

Regards

Roger Govier

Excellent Thank you very much, you have saved a lot of time
Brian

:

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

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