+ Reply to Thread
Results 1 to 2 of 2

Create an external reference link with embedded variable

  1. #1
    Registered User
    Join Date
    10-09-2005
    Posts
    1

    Create an external reference link with embedded variable

    For several years I have used a spreadsheet to balance my checkbook and provide YTD totals by category for a span of the previous 5 years. Each year is a separate workbook and each total is accomplished by an external link to the annual worksheet for that year and the monthly sheet within that workbook An example would be ='C:\Documents and Settings\Greentree\My Documents\[CheckLog 2004.xls]January'!$L8. My problem is that it becomes fairly labor intensive to roll all of these formulas forward each year. It would be much easier if I could replace the file name year (in this case CheckLog 2004.xls) with a variable such as =TEXT(YEAR($A$1),"####")-1, where year is current year. I have experimented on several occasions but can't seem to get the punctuation right to make it work. I would be most grateful to anyone who can provide some assistance........

  2. #2
    Bernard Liengme
    Guest

    Re: Create an external reference link with embedded variable

    Not sure if this will help. but here are three formulas that give the same
    result when C2 holds the entry 1998
    =[Book1998.xls]Sheet1!$F$1
    =INDIRECT("'C:\Documents and Settings\Owner\My
    Documents\Trials\[Book"&C2&".xls]Sheet1'!$F$1")
    =INDIRECT("'[Book"&C2&".xls]Sheet1'!$F$1")

    The last two can be copied down the column to reference other books if C3,
    C4, etc hold other values. If you want to copy across a different cell in
    the book, use =INDIRECT("'[Book"&$C2&".xls]Sheet1'!F$1")

    Note that if the file is not open you get the #REF! error
    Some VBA to open all the files might help
    --
    Bernard V Liengme
    www.stfx.ca/people/bliengme
    remove caps from email

    "Greentree" <[email protected]> wrote in
    message news:[email protected]...
    >
    > For several years I have used a spreadsheet to balance my checkbook and
    > provide YTD totals by category for a span of the previous 5 years.
    > Each year is a separate workbook and each total is accomplished by an
    > external link to the annual worksheet for that year and the monthly
    > sheet within that workbook An example would be ='C:\Documents and
    > Settings\Greentree\My Documents\[CheckLog 2004.xls]January'!$L8. My
    > problem is that it becomes fairly labor intensive to roll all of these
    > formulas forward each year. It would be much easier if I could replace
    > the file name year (in this case CheckLog *2004*.xls) with a variable
    > such as =TEXT(YEAR($A$1),"####")-1, where year is current year. I have
    > experimented on several occasions but can't seem to get the punctuation
    > right to make it work. I would be most grateful to anyone who can
    > provide some assistance........
    >
    >
    > --
    > Greentree
    > ------------------------------------------------------------------------
    > Greentree's Profile:
    > http://www.excelforum.com/member.php...o&userid=27960
    > View this thread: http://www.excelforum.com/showthread...hreadid=474596
    >




+ 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