Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File name as a variable in a formula (Excel 2003)

    Would anyone know how to make a linked filename a variable in a formula?
    For instance, in a worksheet cell I have an employee number and I am doing a vlookup against another file to find the persons salary(among much other info). Every month the outside file is updated and saved with current month as the name.
    When this happens I have to go to each formula and change the name within the formula. This not only is cumbersome, but slow since the changed formula links back to the file for data when the formula changed.
    I am looking for a way to make the filename within the formula a variable that will be picked up from another cell. Changing this one cell will cause the name to change in all formulas.
    Any ideas
    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File name as a variable in a formula (Excel 2003)

    You can use the INDIRECT function for this. Say that you have a formula
    <code>
    ='[OtherWorkbook.xls]Sheet1'!A13
    </code>
    You can change the reference to a string and use
    <code>
    =INDIRECT("'[OtherWorkbook.xls]Sheet1'!A13")
    </code>
    If you put the name OtherWorkbook.xls in cell B4, you can change the formula to
    <code>
    =INDIRECT("'["&B4&"]Sheet1'!A13")
    </code>
    INDIRECT only works if the other workbook is open; Laurent Longre's free add-in Morefunc.dll from Excel add-ins provides a function INDIRECT.EXT that also works if the other workbook is closed.

  3. #3
    New Lounger
    Join Date
    Nov 2005
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File name as a variable in a formula (Excel 2003)

    Thanks
    I'll give it a try

Posting Permissions

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