+ Reply to Thread
Results 1 to 3 of 3

Nested Cell Reference within External Workbook Reference

  1. #1
    Registered User
    Join Date
    05-01-2009
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    2

    Nested Cell Reference within External Workbook Reference

    Hello,

    I'm new to this forum, and i'm having some trouble with a formula that references data from a closed external workbook.

    I currently have this formula which works just fine:

    ='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[24-Mar-09.xls]Sheet1'!$D$8

    However, I want to create a system where the date is inputed into a different cell, (so cell E2 has the text 24-Mar-09) and have the formula reference that cell.

    Essentially I want something like this (if it were only that simple)

    ='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[=E2&".xls"]Sheet1'!$D$8

    I tried doing a nested concatenate formula which also didn't work:

    ='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[=concatenate(E2,".xls")]Sheet1'!$D$8

    Basically, how can I get this nested formula to work, so that I have the external workbook shell reference, which uses the date in a different cell to specify which workbook to retrieve the data from.

    I have been struggling with this for too long now, so any help would be much appreciated.

    Thank you!

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Nested Cell Reference within External Workbook Reference

    All dates in Excel are held as a number, e.g. 24-Mar-09 is held as 39896.
    So you have to convert the number into the format you want using functions.
    Try this (untested):

    ="C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\["&DAY(E2)&"-"&MONTH(E2)&"-"&RIGHT(YEAR(E2),2)&".XLS]Sheet1"!$D$8
    Last edited by Special-K; 05-01-2009 at 08:00 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    05-01-2009
    Location
    New York, NY
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Nested Cell Reference within External Workbook Reference

    Thank you, I appreciate the response, but the formula did not work. Also the date formatting is a non-issue because I already converted it into text using the text function.

    I just need a way to reference a closed external workbook, with a cell reference within that reference.

    The function below works fine:
    ='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[24-Mar-09.xls]Sheet1'!$D$8

    I just need a function similar to this:
    ='C:\Documents and Settings\dedalus\Desktop\Excel Import Commodity Prices\[=E2.xls]Sheet1'!$D$8

    Where cell E2 has 24-Mar-09 inputted as text .

    Thanks!

+ 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