I have a Workbook with 12 Worksheets. The first Worksheet is the Index, the 11 other sheets contain data relative the index. This Workbook references a file tree structure, what I am trying to do is link all Folder names to their respected Worksheet. I have labelled these folders as 'Folder 1, Folder 2' etc. In reality the folder names are all unique.

The table below is the folder tree.

I need to name the worksheets based on their breadcrumbs. So For example Folder 3 Would be

Folder 1>Folder 2>Folder 3

With the limit being set as 31 characters, Any directories 6 deep with long names wouldn't fit. So I have the table below.

How would I go about doing this? The worksheets are in order, I just need to batch rename based on their path and link to the first table, so when they click on Folder 8, they will hit tab F1.F6.F8

CodePudding user response：

This seems to be a potential duplicate of Excel - Hyperlink to a worksheet which name is found in a cell over at Stackoverflow Superuser

This answer shows how to use the Hyperlink function in excel to take a value from a cell (your worksheet name column) and using this to link to another tab

=HYPERLINK(CONCATENATE("#", [cellcontainingthetabname], "!A1"), [textyouwishtodisplay])