Forum Discussion
workbook and sheet name via formula
Afraid for Excel Online that's only with Office Script, there are properties getName both for workbook and worksheet.
VBA and related functions don't work in Excel Online.
This is the most recent method I was able to get working.
The only drawback is that changes to sheets need can only update after refreshing the query in the desktop app, but it does leave room for someone more capable with VBA and OfficeScripts/TypeScript.
I also had a thought that Fabric/PowerBI connections might help with compatability... ANYWAY!
Important: Must be done in desktop app at this stage but the links persist online
Stage 1
Data > Add Source > from Web > Sharepoint file URL (from details tab in SharePoint online NOT from URL bar) > Use 'Organization Login' option to authenticate
Stage 2
R-Click top folder & select Transform Data > filter down Columns to Name & Hidden (if you want to filter out hidden tabs) with sheet name & Hidden yes or no
Stage 3
Save and Load Data (Renaming the query first will make it easier to reference)
Stage 4
On the newly created sheet with your table, add column on the end of the table called "Links" > first cell formula below
=HYPERLINK("#'"&[@[Name]]&"'!A1","Go to Sheet")
Probably have a couple bits missing, but i can clarify if people ask.