Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
excel [2018/12/07 11:56]
gerardorourke
excel [2020/02/07 14:09] (current)
gerardorourke
Line 116: Line 116:
 End Sub End Sub
 </​code>​ </​code>​
 +
 +==== Macro to create link for each worksheet ====
 +
 +=== Get a list of all worksheets in your workbook ===
 +
 +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
 +  *https://​www.ablebits.com/​office-addins-blog/​2014/​09/​30/​dependent-cascading-dropdown-lists-excel/​