Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    May 2008
    Location
    Ottawa, Ontario
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup (2003 SP3)

    I have a set of 23 files with about 100 tabs in total that are used to do financial analysis. There are 15 fields on each tab that are updated with new information for the analysis. I am in the process of changing these files to pull the information from a separate excel file ("data source file") using vlookup. I want to be able to pull the "table_array" from a cell on each tab that referres to another separate file ("the location file") where the file name, range name and location would be stored as a string of text, like,H:ProjectsReview File AutomationPeriod 2 RC Summary (John Perkins).xls!P2_DATA. The data source file and the location file where the location is entered are storred in the same folder on a network. I have not been able to accomplish this. The location, file name and range name are enterred on the separate file so that I only have to change this once each month, quarter, etc.
    Does anyone have any ideas? Thank you for any help anyone can give me. I am going home now so I won't be able to respond until tomorrow.

    Thank you very much,

    John Perkins
    Ottawa, Canada

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

    Re: vlookup (2003 SP3)

    Look up the INDIRECT function - you can use this to refer to a cell or range whose address is provided as a string or string expression.
    Note 1: INDIRECT works only with a reference to an external workbook if that workbook is open in Excel.
    Note 2: There's a free add-in called Morefunc that contains a function INDIRECT.EXT that works with external references even if the workbook is closed.
    The author's website seems to have been closed down, but if you search for Morefunc you should still find download sites.

  3. #3
    New Lounger
    Join Date
    May 2008
    Location
    Ottawa, Ontario
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2003 SP3)

    attaching referenced files separately, as it seems you can only attach one file per post.

    JP

    Further to my query of 09-Jul-08 <post:=720,779>post 720,779</post:>, I am attaching an example of what I want to accomplish. The files vlookup2.xls, vlookup2data.xls, and vlookup3data.xls contain what I hope to be able to accomplish. The file vlookup2.xls has two areas where data is pulled from vlookup2data.xls, and vlookup3data respectively. I want to beable to put the file name of the data source in a cell or multiple cells and have the vlookup formula use that file name in the “Table_array” field of the formula.

    HansV’s response of 09-Jul-08 <post:=720,784>post 720,784</post:> suggested I use the INDIRECT function to accomplish this. I referred to http://www.contextures.com/xlFunctions05.html (Section “Create a Reference To a Different Workbook “) to try to learn how to do this and as of this time I have not been able to get this to work. I attach the files where I am trying this (TestFile.xls and Test Indirect.xls).

    Can anyone help me with this?

    Thank you,

    John P
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    May 2008
    Location
    Ottawa, Ontario
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2003 SP3)

    attaching vlookup3data.xls
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    May 2008
    Location
    Ottawa, Ontario
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2003 SP3)

    attaching TestFile.xls
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    May 2008
    Location
    Ottawa, Ontario
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2003 SP3)

    attaching Test Indirect.xls
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    May 2008
    Location
    Ottawa, Ontario
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2003 SP3)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Further to my query of 09-Jul-08 <post#=720,779>post 720,779</post#>, I am attaching an example of what I want to accomplish. The files vlookup2.xls, vlookup2data.xls, and vlookup3data.xls contain what I hope to be able to accomplish. The file vlookup2.xls has two areas where data is pulled from vlookup2data.xls, and vlookup3data respectively. I want to beable to put the file name of the data source in a cell or multiple cells and have the vlookup formula use that file name in the “Table_array” field of the formula.

    HansV’s response of 09-Jul-08 <post#=720,784>post 720,784</post#> suggested I use the INDIRECT function to accomplish this. I referred to http://www.contextures.com/xlFunctions05.html (Section “Create a Reference To a Different Workbook “) to try to learn how to do this and as of this time I have not been able to get this to work. I attach the files where I am trying this (TestFile.xls and Test Indirect.xls).

    Can anyone help me with this?

    Thank you,

    John P
    Attached Files Attached Files

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

    Re: vlookup (2003 SP3)

    For the future: you can attach only one file to a post, but you can add multiple files to a zip file and attach the zip file.
    Windows XP and Vista have built-in support for creating zip files: File | Send To | Compressed (zipped) folder.

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

    Re: vlookup (2003 SP3)

    Here is a working formula for use in TestIndirect.xls:
    <code>
    =INDIRECT("'["&A2&"]"&A3&"'!"&A4)
    </code>
    (You misplaced single and double quotes in several places)

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

    Re: vlookup (2003 SP3)

    You can use the following formula in cell B2 on Sheet1 in vlookup2.xls:
    <code>
    =VLOOKUP(B1,INDIRECT($B$6&"!compdata"),2)
    </code>
    The formula takes the file name from cell B6 and concatenates it with !compdata; this is the argument for INDIRECT.

  11. #11
    New Lounger
    Join Date
    May 2008
    Location
    Ottawa, Ontario
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (2003 SP3)

    Thank you HansV your two solutions work perfectly!!

    Regards

    John Perkins

Posting Permissions

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