Aug 09 2022 11:39 AM - edited Aug 09 2022 11:58 AM
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!
Aug 09 2022 12:32 PM
Try adding the path to the hyperlink address.
Aug 09 2022 01:15 PM
@Sergei Baklan 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.
Aug 09 2022 01:39 PM
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.
Aug 09 2022 02:41 PM
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.