+ Reply to Thread
Results 1 to 2 of 2

using a cell reference as part of a formula

  1. #1
    Registered User
    Join Date
    10-07-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    using a cell reference as part of a formula

    Hi,

    I have a workbook that uses a few formulas to get data from another workbook. Since the name of the workbook that the main spreadsheet feeds off changes every month, I want to put a reference to a cell in the formula so that I can get the formulas to refer to the new file name:

    This is one of the formulas I'm using:

    ='G:\Risk Management\DailyReports\ABS\090929\[29-September-09.xls]intex data analysis'!FB12

    And I would like to be able to change this part of the formula: 090929\[29-September-09.xls] to being a reference to another cell (or cells) but when I put a cell reference in the formula it doesn't seem to work.

    Is there a way to do this?

    Many thanks,

    Luca

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: using a cell reference as part of a formula

    The general approach for creating "dynamic" links using text strings is INDIRECT function, however, this function has one notable clause - it can only work where target is open (it is also Volatile).
    If your target file is not always open when the file containing the formulae is in use then I'm afraid INDIRECT is a no-go, you can however investigate Laurent Longre's morefunc.xll and specifically the INDIRECT.EXT function should you so wish.

    Generally speaking if you are going to use links you are better off running an Edit -> Replace each month updating as and where appropriate.

+ 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