+ Reply to Thread
Results 1 to 7 of 7

Dynamic External Workbook reference

  1. #1
    Co-op Bank
    Guest

    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


  2. #2
    blackmot
    Guest

    RE: 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
    >


  3. #3
    Roger Govier
    Guest

    Re: 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
    >


  4. #4
    Co-op Bank
    Guest

    Re: 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
    > >

    >


  5. #5
    Registered User
    Join Date
    12-15-2005
    Posts
    3

    ComboBox in Excel

    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

  6. #6
    Registered User
    Join Date
    12-15-2005
    Posts
    3

    My availability

    My availability [email protected]

    MSN: [email protected]

    Balázs

  7. #7
    Roger Govier
    Guest

    Re: 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
    >>>

    >>


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1