Create (or Recreate) TOC for spreadsheet:
Sub createTOC()
Const TOC_NAME = "TOC"
Dim oSheets As Variant
Dim oNames As Variant, oRes As Variant
Dim oSheet As Variant
Dim i As Long, j As Long
oSheets = ThisComponent.getSheets()
Rem Delete old TOC if it exists
If oSheets.hasByName(TOC_NAME) Then oSheets.removeByName(TOC_NAME)
oNames = oSheets.getElementNames()
ReDim oRes(UBound(oNames))
For i = LBound(oNames) To UBound(oNames)
oRes(i) = Array("=HYPERLINK(""#" +oNames(i) + """;""Jump to " + oNames(i) + """)")
Next i
Rem Create new (empty) TOC
oSheets.insertNewByName(TOC_NAME,0)
oSheet = oSheets.getByIndex(0)
Rem Paste hyperlinks from cell A12
oSheet.getCellRangeByPosition(0, 11, 0, 11+UBound(oNames)).setFormulaArray(oRes)
End Sub
Update. @calcboy, it seems to me that I have found an acceptable solution for the second problem from your question, “provide quick links from one sheet to another based on their contents”. It doesn’t require a macro.
Let’s say you want to place a hyperlink to the original formula range to the right of a cell with a formula like =AVERAGE($Sheet6.E15:E19)
.
Please do the following. Select cell B2 on any sheet, press Ctrl+F3 to open the Manage Names and add new name (for example JumpTo).
To the field “Range or formula expression” paste this formula:
HYPERLINK("#"®EX(FORMULA(A1);"'?[a-zA-Z0-9\s\[\]\. ]{1,99}'?\.\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?");"(See "®EX(FORMULA(A1);"'?[a-zA-Z0-9\s\[\]\. ]{1,99}'?\.\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?")&")")
Now, to the right of any cell with a formula that refers to another sheet, you can enter =JumpTo
and get (most often) a hyperlink to the original range