Since thread68-1260610 was closed. I thought I would post my solution here.
Found a MUCH easier way to do this with limited formulas and coding (how to put the tab name reference and Hyperlink in the same cell in a summary worksheet):
1. Create the tab name reference in a cell in each worksheet (as explained in the prior thread) using the formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) - make the filename your tab name. In my example, I put this in cell B1 in every worksheet
2. In the Summary Worksheet (where all the summary data is displayed for all the tabbed worksheets), click in the cell where you want the first worksheet Tab Name and Hyperlink to appear, and set the cell value = the location in each worksheet (B1) where you put the above formula.
3. Right click the cell and select Hyperlink option from the menu selection.
4. When the Insert Hyperlink Popup window appears, select "Place In This Document" from "Link To:" choices at left.
5. Select Place in this document by clicking on the correct Tab.
6. Update the Cell Reference above that with the correct cell location in each worksheet for the filename done in step 1.
Click OK.
You are done. (note, if you ever change the tab names in the future, it will automatically update with no further changes required)
Found a MUCH easier way to do this with limited formulas and coding (how to put the tab name reference and Hyperlink in the same cell in a summary worksheet):
1. Create the tab name reference in a cell in each worksheet (as explained in the prior thread) using the formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) - make the filename your tab name. In my example, I put this in cell B1 in every worksheet
2. In the Summary Worksheet (where all the summary data is displayed for all the tabbed worksheets), click in the cell where you want the first worksheet Tab Name and Hyperlink to appear, and set the cell value = the location in each worksheet (B1) where you put the above formula.
3. Right click the cell and select Hyperlink option from the menu selection.
4. When the Insert Hyperlink Popup window appears, select "Place In This Document" from "Link To:" choices at left.
5. Select Place in this document by clicking on the correct Tab.
6. Update the Cell Reference above that with the correct cell location in each worksheet for the filename done in step 1.
Click OK.
You are done. (note, if you ever change the tab names in the future, it will automatically update with no further changes required)