Linking to other workbooks with a calculated referance

    Hi,


    I know you can link to another workbook in a formula, for example, =[mydata.xls]Sheet2!$E$1


    My problem is the data workbook can change depending on the department and month and is stored in the cell F3.


    I cannot seem to get =[f3]Sheet2!$E$1 to work. (F3 contains the text "mydata.xls")


    The full path to the data is C:\Documents and Settings\my username\My Documents\PROJECT\ Report Data\mydata.xls


    Any ideas please?

    Re: Linking to other workbooks with a calculated referance


    you can use the indirect function
    BUT the file will need to be open to do that


    to access a closed workbook indirect.ext add-in needs to be added


    =indirect( "["&f3&"]Sheet2!$E$1" ) to work.

    ETAF

    Re: Linking to other workbooks with a calculated referance


    Many thanks!


    Where do I find the indirect.ext add-in as the only ones that I have listed (Excel 2003) are Analysis TookPak, Analyis ToolPak VBA, Conditional Sum Wizard, Euro Currency Tool, Internet Assistant VBA, Lookup Wizard and Solver Add-In?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login