+ Reply to Thread
Results 1 to 4 of 4

vlookup variable filepath one file variable can this be done via a function?

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    2

    vlookup variable filepath one file variable can this be done via a function?

    need to look up data across files. the file is held in the user area (don't ask why) and hence the file path changes depending on the user. I know how to find the user and hence know the full filepath but vlookup doesn't like variables.

    Indirect and Indirect.ext only work if the files are all open which is no good.

    Pull is too slow and doesn't always update.

    Any ideas? is there a way to creat teh filepath on opening the file that vlookup will like since the only variable is the user?

    any help greatly appreciated (not much of a coder I'm afraid)

  2. #2
    Registered User
    Join Date
    03-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: vlookup variable filepath one file variable can this be done via a function?

    I did try the following but still variable so vlookup didn't accept it.
    Function Usrname() As String
    Usrname = Environ("username")
    End Function
    Function pbfilearray() As String
    'aim to create filearray for vlookup
    pbfilearray = "'C:\Users\" & Usrname() & "\GD\SDE\[File.xlsm]inc lccy comm'!$A:$AZ"
    End Function

    then in vlookup(a1,pbfilearray(),3,false) but gives answer of #Value!

  3. #3
    Forum Contributor
    Join Date
    09-24-2012
    Location
    York, England
    MS-Off Ver
    Excel 2003/07/10
    Posts
    165

    Re: vlookup variable filepath one file variable can this be done via a function?

    I was reading, as part of another problem that it is possible to read individual cells from a closed workbook. If this is so and you can't find a way to open it, then with a known structure for the look up table, you could build this cell by cell.
    Having built the table in an active sheet, use it as your look up source. This assumes that you don't need to update the (closed)workbook, and you can set up an arrangement to be advised of any changes made to the table by persons other than you. Hope it works for you.

  4. #4
    Forum Contributor
    Join Date
    09-24-2012
    Location
    York, England
    MS-Off Ver
    Excel 2003/07/10
    Posts
    165

    Re: vlookup variable filepath one file variable can this be done via a function?

    I decided to test this out, so I created a table of numbers in a file called Table.xlsx located at F20:J41 in Sheet1. I saved the file to C:\Temp and closed it.
    Then I opened up a new workbook and entered the following into cell F20(sheet1):

    ='C:\Temp\[Table.xlsx]Sheet1'!F20
    I then copied this formula to all cells F20:J41 and this replicated Table.xlsx without that workbook being open! I was then able to reference this replicated data in other formulae(eg VLOOKUP). Excel establishes a link with the original data and will prompt you to update for any amendments to the original data when you open the replications book.
    There is a downside however. If the user makes structural alterations to the table as opposed to simply altering the values, you would need to be advised as such amendments might not be picked up by Excel (eg extending the size of the table). Of course it is likely that this problem is there whichever way you handle the task.

    Hope that helps and good luck!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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