Forum Discussion

TEXASFOREVER1936's avatar
TEXASFOREVER1936
Copper Contributor
Aug 09, 2022

XLOOKUP TO RETUN HYPERLINK.

I have a spread sheet named "One Size Look Up (OSLU)" that can return numerous values of a certain criteria with XLOOKUP functions. I am trying to format a cell, in OSLU, to return a hyperlink to a local file on my PC that would route me to the criteria's profile. The hyperlink is located on a separate spread sheet named "CHARTS" within the same workbook. I have wrapped my XLOOKUP in a hyperlink formula and it returns the error message "CANNOT OPEN THE SPECIFIED FILE". The hyperlink opens correctly in its originated spread sheet, CHARTS. The return value of the XLOOKUP, wrapped in the hyperlink formula, does not. Any ideas? 

 

Thank you so much! 

Excel
    • TEXASFOREVER1936's avatar
      TEXASFOREVER1936
      Copper Contributor
      Aug 09, 2022

      SergeiBaklan When I try "=HYPERLINK((XLOOKUP(C16,CHARTS!$GO$4:$GO$432,XLOOKUP(OSLU!D16,CHARTS!$GQ$2:$HA$2,CHARTS!$GQ$4:$HA$432))))" I get the following results when I click on the returned Hyperlink (see attached). Also, my hyperlink address is not routed to Onedrive nor is the file I'm trying to Hyperlink. It is stored on a local drive.

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Aug 09, 2022

        TEXASFOREVER1936 

        It is forwards on OneDrive if you current file is located on OneDrive, doesn't matter where is the target file. Its file path is not recognized correctly, thus Excel takes current folder and tries to find file here.

         

        Most probably your issue is not in XLOOKUP formula, but in hyperlink it returns. Something shall be corrected here or by formula. Without knowing which exactly link text formula returns it's hard to say more concrete.

         

        As an example, if my current file is on OneDrive and target file is local drive, link returned as '[C:\Test\testA.xlsx]Sheet2'!$A$1 forwards on OneDrive, [C:\Test\testA.xlsx]'Sheet2'!$A$1 opens local file and focuses on right place.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 09, 2022

    TEXASFOREVER1936 

    If all the files are in the same location, you might want to try adding a Hyperlink Base to the workbook's properties.

    File | Info | Properties | Advanced Properties.
    My Hyperlink base for the test I ran: C:\Users\MyUserName\MyOneDriveName\Desktop\Test
    Then in the sheet I can use HYPERLINK on the name of the file to open. Path not needed.

     

Share