Click here to download the file used in the below example.

In Excel usually, we save the folder locations for easy reference. But most of the times it will not work when there is any change in the folder name.

Now let us learn HYPERLINK and TEXTJOIN both excel functions in combination helps to get rid these issues.

Let us see below sample before proceeding further.

excel hyperlink formula to another sheet
Excel Dynamic Hyperlink

Explanation Excel Functions

TEXTJOIN Formula used in cell C5=TEXTJOIN(“\”,TRUE,A5:B5)

Note: You can check the TEXTJOIN formula updated in D Column

HYPERLINK Formula used in cell E5=HYPERLINK(C5,”SampleXL1″)

Note: You can check the HYPERLINK formula updated in F Column

  1. Firstly we have to update the list for folders names. In the above example Cell A5 =D:\Excel we have given 1st Folder Name then in Cell B5=sample Excel 1.xlsx, we have updated the filename.
  2. If you have multiple folders simply update all folders in different cells or else you can give the entire path in one single cell.
  3. Then with the help of TEXTJOIN function, we can make proper File or Folder location.
  4. If we look at Syntax of TEXTJOIN function 1st argument we can give the delimiter so to create a folder location we need “\” symbol after every folder name we can use this as a delimiter.
  5. 2nd Argument is ignore_empty here we have an option to include the blank cells but for this case keep it TRUE, so that we can skip the blank cells.
  6. text1,[text2]…….From 3rd argument onwards is we have to give the range of cells which contain text and want to join into a single text. Above example, we have given as A5: B5. Now file/folder Location is created.
  7. If there is any change in folder or file name simply edit the specific cell information.
  8. To learn more about TEXTJOIN function click here.
  9. Now simply use HYPERLINK function and in place of link_location 1st argument give the result of TEXTJOIN function.
  10. For easy reference sake in HYPERLINK function, we have an optional 2nd argument which is [friendly_name]. Under this argument, we can give any name to easily recognise the link or else the result will be the entire file/folder location name, which doesn’t look good. In the above example, we have given SampleXL1, SampleXL2 & SampleXL3.
  11. Now if we click the Result of HYPERLINK function it will easily route to a specific folder/file. In the above example if we have to click on SampleXL1, SampleXL2 & SampleXL3 it will open the respective excel workbook.

Note: Don’t forget to give the file extension name if you want to open a file. Like .xls .xlsx .pdf…. so on.