Reference a cell's content into a formula

[MrDB4Excel]

I am creating a TOC using the following parameters I found on How To Excel's webpage regarding How To Generate A List Of Sheet Names From A Workbook Without VBA
Go to the Formulas tab.
Press the Define Name button.
Enter SheetNames into the name field.
Enter the following formula into the Refers to field.
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Hit the OK button.
"In a sheet within the workbook enter the numbers 1,2,3,etc… into column A starting at row 2.
Then in cell B2 enter the following formula and copy and paste it down the column until you have a list of all your sheet names."
=INDEX(SheetNames,A2)
As a bonus, we can also create a hyperlink so that if you click on the link it will take you to that sheet. This can be handy for navigating through a spreadsheet with lots of sheets. To do this add this formula into the column C.
=HYPERLINK("#'"&B2&"'!A1","Go To Sheet")
=HYPERLINK("#'"&B2&"'!A1","F1")
This last formula is where I want to replace the "Go To Sheet" with the contents of the sheet names that occur in column F as follows:
Table Of Contents
TOC
Table of Contents Gallery
Translation Websites
CZ2ENG
Comments-Notes
Etc.
Thus when done the Links column would read the content from F1, F2, F3 etc.
But this is what I get instead:
Sheet Names Sheet Links
1 Table Of Contents &F1
Where the formula used for the Sheet Links column reads as follows:
=HYPERLINK("#'"&B2&"'!A1","&F1")
In essence I want each link to read as the sheet name and not "Go To Sheet"
What am I missing here.
Any help is much appreciated.

 

[FormR]

Where the formula used for the Sheet Links column reads as follows:
=HYPERLINK("#'"&B2&"'!A1","&F1")

Hi - try removing the quotes and ampersand from the F1 cell reference - like this.

=HYPERLINK("#'"&B2&"'!A1",F1)

 

[MrDB4Excel]

Many thanks FormR. Works perfectly!!!