Differences

This shows you the differences between two versions of the page.

--- excel [2020/02/03 08:39] – [Macro to create link for each worksheet] gerardorourke
+++ excel [2020/02/07 14:09] (current) – gerardorourke
@@ Line 119: / Line 119: @@
 ==== Macro to create link for each worksheet ====
-  *Use a formula to create hyperlink to all workssheets
+=== Get a list of all worksheets in your workbook ===
- <code>=HYPERLINK("#'"&B1&"'!A1",B1)</code>
+Reference: https://www.datanumen.com/blogs/3-quick-ways-to-get-a-list-of-all-worksheet-names-in-an-excel-workbook/
+  * Formulas tab and click the “Name Manager” button -> New
+  * Enter **ListSheets** as the **Name** and the below formula into the **Refers to** field.
+  * <code>=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")</code>
+  * Have a worksheet with A column with number of sheets on each row. and Use a formula to create hyperlink to all worksheets
+  * <code>=INDEX(ListSheets,A1)</code>
+  * then use the below to get the URL link for the worksheet
+  * <code>=HYPERLINK("#'"&B1&"'!A1",B1)</code>
 Reference: https://www.datanumen.com/blogs/3-methods-to-create-a-list-of-hyperlinks-to-all-worksheets-in-an-excel-workbook/
+Dependant Dropdowns
-Reference: https://www.get-digital-help.com/create-links-to-all-sheets-in-a-workbook/
+  *https://www.ablebits.com/office-addins-blog/2014/09/30/dependent-cascading-dropdown-lists-excel/
-<code vb>
-Sub CreateLinksToAllSheets()
-Dim sh As Worksheet
-Dim cell As Range
-For Each sh In ActiveWorkbook.Worksheets
-    If ActiveSheet.Name <> sh.Name Then
-        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
-        "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
-        ActiveCell.Offset(1, 0).Select
-    End If
-Next sh
-End Sub
-</code>