View original (French)

Excel VLOOKUP Function with Relative Path

Solved
Hasaran -  
 Hasaran -
Bonjour,

I would like to use the VLOOKUP function in Excel by calling a matrix via a relative path.
VLOOKUP(variable;'C:\path\[file.xls]Sheet1'!$C$4:$N$499;10;FALSE)

I created a macro that gives me in cell A1 the location of my spreadsheet (thanks to ActiveWorkbook.Path).

I then concatenate this result (C:\path) like this: CONCATENATE("'";A1;"\[file.xls]Sheet1'!$C$4:$N$499")
in cell A2.

But when I rewrite my VLOOKUP function like this: VLOOKUP(variable;A2;10;FALSE)
the cell displays #N/A

How could I solve this problem? 
Configuration: Excel 2003

4 answers

  1.
    Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
     
    Hello,
    Thank you for the information.

    In the sense that both workbooks are in the same folder, use your formula:
    VLOOKUP (variable,'C:\path\[file.xls]Sheet1'!$C$4:$N$499,10,FALSE), which will work even if the source workbook is closed.

    The other solution with the [INDIRECT] function requires that the source workbook be open as well. If not open in memory, the function returns an error value #REF!
    To use this option:
    In the target workbook, put in a text format in a cell:
    A1=C:\path\[file.xls]Sheet1
    A2 = C4:N499
    and the INDIRECT function to insert in VLOOKUP becomes: INDIRECT("'"& A1 "'!"&A2)

    --

    Regards.
    Jean-Pierre
    1
    1. Hasaran
       
      Here it is, it works!

      Too bad INDIRECT doesn't work with the database file closed...

      Thank you for your help.
      0
  2. Raymond PENTIER Posted messages 58211 Registration date   Status Contributor Last intervention   17 480
     
    Maybe with the INDIRECT function?
    --
    Retirement is great! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0
    1. Hasaran
       
      I'm trying to use the INDIRECT function:
      VLOOKUP(variable;INDIRECT($A$3);10;FALSE)
      by putting in A3 the reference of the cell where I have my concatenated path (A2).

      Same result .... (it works outside the VLOOKUP function)

      unless there's another way to use the INDIRECT function ....
      0
  3. Le Pingou Posted messages 12273 Registration date   Status Contributor Last intervention   1 476
     
    Hello,
    According to your message, cell [A2] contains the path to your matrix, so just use: INDIRECT($A$2)!

    Is the matrix ('C:\path\[file.xls]Sheet1'!$C$4:$N$499) still the same?

    Does your formula: VLOOKUP(variable,'C:\path\[file.xls]Sheet1'!$C$4:$N$499,10,FALSE)
    work Yes or No?
    --

    Regards.
    Jean-Pierre
    0
    1. Hasaran
       
      Hello,

      By using INDIRECT($A$2), the cell displays #REF! whether integrated into VLOOKUP or independently.

      The array ('C:\path\[file.xls]Sheet1'!$C$4:$N­$499) is always the same.

      My formula: VLOOKUP(variable,'C:\path\[file.xls]Sheet1'!$C$4:$N­­$499,10,FALSE) works well.
      0
  4. Raymond PENTIER Posted messages 58211 Registration date   Status Contributor Last intervention   17 480
     
    Hello.

    If I followed everything, the matrix containing the data is not in the same folder (file) as the sheet where the lookup formula is? In this case, let's remember that it is IMPERATIVE that the former is opened for the lookup to take place.

    I didn't understand why you wrote INDIRECT($A$3) when the content of A3 seems to be =A2 ... Then you could just write INDIRECT(A2)!

    As for the content of A2, I didn't quite understand what you were trying to do: I don't see the meaning of 'A1\ in 'A1\[file.xls]sheet1'!$C$4:$N$499
    But since you said that "it works outside of the VLOOKUP function", I have to assume that it's correct ...

    Final observation: it is always beneficial to give a name to the data matrix; thus if you name the range C4:N499 of the sheet1 in the file.xls, your formula can be simplified to 'A1\[file.xls]Matr

    --
    Retirement is great! Especially in the Caribbean ... :-)
    ☻ Raymond ♂
    0
    1. Hasaran
       
      Hello.

      The matrix containing the data is in my case in the same folder but not in the same file as the sheet where the lookup formula is located.
      The search works fine when the file containing the data is closed, the only difference I noticed is that Excel asks me to update the file when the one containing the data is closed.

      Writing INDIRECT(A2) does not work, I get the #REF! display in the cell whether I integrate INDIRECT into VLOOKUP or put INDIRECT in a separate cell.

      My first objective is to be able to work in the same folder containing the data and work files regardless of the folder's location on the hard drive.
      I am initially looking to separate the steps:
      - not having found a ready-made formula to use a relative path in the VLOOKUP function, I found that the ActiveWorkbook.Path function used in a macro allowed me to obtain the absolute path of the folder I am working in. I displayed this path in cell A1.
      This cell A1 then contains C:\path.
      - I need the complete path of the matrix to insert it into the VLOOKUP function. Since the file containing the matrix is in the same folder as the file in which I use VLOOKUP, I just need to add the part of the path that will always be the same: \[file.xls]sheet1'!$C$4:$N$499. I therefore concatenate it with the content of cell A1.
      At this stage, I have in cell A2 the complete path of my matrix, regardless of my working folder: 'C:\path\[file.xls]sheet1'!$C$4:$N$499 (I also had to add an apostrophe at the beginning)
      - I then replace the complete path of the matrix in the VLOOKUP function with A2 which contains this path.

      As for assigning a name to the matrix, I would like, if it's not a problem for this first goal I'm setting, to forget about it because it is causing me issues.
      0