Forum Discussion

liverlarson's avatar
liverlarson
Brass Contributor
Sep 18, 2020
Solved

workbook and sheet name via formula

I have a standard header I put on almost every excel workbook I create I have been using for years. It includes several standard items, including Workbook name and Worksheet name, which are extracted...
excel
Excel for web
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 18, 2020

    liverlarson 

    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.

rcstha's avatar
rcstha
Copper Contributor
Jan 10, 2024

works great but for active worksheet only..actaually i want sheetname in all sheet with its own sheetname.　Thank youNikolinoDE 

Jan_Wijninckx's avatar
Jan_Wijninckx
Brass Contributor
Jan 10, 2024

rcstha - you just need to put the formula on each sheet. Just select which environment your workbook has to work in: Desktop or SharePoint/Web. 

Desktop: 

=MID(CELL("filename",'Sheet1'!A2),FIND("]",CELL("filename",'Sheet1!A2))+1,32)
(thanks NuggetSA )

SharePoint/Web: 

=SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D2), "!"), "'", "" ), "=", "")

with D2 containing:
='Sheet1'!D1 formatted as 0;0;
The latter is easy to create: place your cursor on D2 type = switch to another sheet, then switch back to Sheet1, and click on D1. Done.

  • rcstha's avatar
    rcstha
    Copper Contributor
    Jan 10, 2024

    Thank youJan_Wijninckx 

    i am using desktop and got exact answer by using the formula

    =TEXTAFTER(CELL("filename",A1),"]")

     

    • Jan_Wijninckx's avatar
      Jan_Wijninckx
      Brass Contributor
      Jan 11, 2024
      Brilliant - even shorter, thank you!
      Introduced on 19 October 2022, O365 only. So keep in mind which version you have 🙂

Resources