Update VLOOKUP formula path RRS feed

  • Question

  • I have many formulas that are all using the VLOOKUP function and referencing an external file.

    For example

    =VLOOKUP($B$7,'\\server\share\folder1\folder2\[sample file 12042018.xls]Sheet1'!$A$1:$D$1000,2,0)

    Is there a way to change the path to the sample file based on the value of a cell?

    I want the user to be able to browse to a file, select it, and put the entire path with file name in a cell.

    Then refer to that cell

    I have the browse, select file and place file path in cell part.

    How can I change the VLOOKKUP to the new file?


    Tuesday, December 4, 2018 6:08 PM

All replies

  • Assume that cell Q1 contains the following path and file name

    \\server\share\folder1\folder2\[sample   file 12042018.xls]

    Use Indirect like the following.

    =VLOOKUP($B$7,INDIRECT("'" & Q1 & "Sheet1'!" & "$A$1:$D$1000"),2,0)

    Note that the concatenated string after INDIRECT( commences with a single quote inside double quotes and then concatenate & Q1 & "Sheet1'!" & "$A$1:$D$1000")

    there is another single quote after Sheet1

    Regards, OssieMac

    Wednesday, December 5, 2018 12:28 PM