Hi everyone !
I am trying to set a reference to external directory path in order to use it for hyperlink function (The hyperlink is used to either open the directory, sub-directories and sub files within these directories)
to do that I'm using the following functions:
1. To set the main directory location: =(LEFT(CELL("filename"),-1+FIND("\D",CELL("filename")))&"\Cases\") 'This return the main directory path where the sub-directories and files are created and saved.
I've assigned the Ref.name of "Claim_path" for this function.
2. The hyperlink for the sub-directories and sub-sheets is: =HYPERLINK(CONCATENATE(Claims_Path,[@YEAR],slash,[@[File No]],slash,[@[File No]],".xlsx"),CONCATENATE("File #",[NO.])) 'slash is a Ref name assigned for the slash sign "\", I'm using a referenced table with headers [Year] and [File No]
No after clicking the hyperlink the sheet opened but it directly become error "#Value". after refreshing the sheet this error eliminated but whenever I click the hyperlink for the sheet, the error happens again.
I am trying to set a reference to external directory path in order to use it for hyperlink function (The hyperlink is used to either open the directory, sub-directories and sub files within these directories)
to do that I'm using the following functions:
1. To set the main directory location: =(LEFT(CELL("filename"),-1+FIND("\D",CELL("filename")))&"\Cases\") 'This return the main directory path where the sub-directories and files are created and saved.
I've assigned the Ref.name of "Claim_path" for this function.
2. The hyperlink for the sub-directories and sub-sheets is: =HYPERLINK(CONCATENATE(Claims_Path,[@YEAR],slash,[@[File No]],slash,[@[File No]],".xlsx"),CONCATENATE("File #",[NO.])) 'slash is a Ref name assigned for the slash sign "\", I'm using a referenced table with headers [Year] and [File No]
No after clicking the hyperlink the sheet opened but it directly become error "#Value". after refreshing the sheet this error eliminated but whenever I click the hyperlink for the sheet, the error happens again.