SOLVED

workbook and sheet name via formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1681449%22%20slang%3D%22en-US%22%3Eworkbook%20and%20sheet%20name%20via%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1681449%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20standard%20header%20I%20put%20on%20almost%20every%20excel%20workbook%20I%20create%20I%20have%20been%20using%20for%20years.%20It%20includes%20several%20standard%20items%2C%20including%20Workbook%20name%20and%20Worksheet%20name%2C%20which%20are%20extracted%20via%20CELL(%22Filename%22%2CA1)%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Excel%20workbooks%20that%20are%20being%20viewed%20in%20Excel%20Online%2C%20however%2C%20whether%20through%20onedrive%2C%20sharepoint%2C%20or%20whatever%2C%20this%20formula%20yields%20a%20%23VALUE!%20error.%20When%20I%20open%20in%20desktop%2C%20it%20renders%20fine%2C%20just%20not%20when%20looking%20at%20browser%20window.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20an%20alternative%20that%20I%20can%20use%20to%20display%20current%20workbook%20and%20worksheet%20names%20via%20a%20formula%20in%20a%20cell%20that%20is%20compatible%20with%20Excel%20Online%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1681449%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1682167%22%20slang%3D%22en-US%22%3ERe%3A%20workbook%20and%20sheet%20name%20via%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571483%22%20target%3D%22_blank%22%3E%40liverlarson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20need%20to%20create%20a%20Name%20like%20%22SheetName%22%20and%20use%20GET.CELL(32%2CA1)%20in%20the%20Refers%20To%20area.%20Whenever%20you%20need%20the%20sheet%20name%20you%20need%20to%20type%20%22%3DSheetName%22%20in%20the%20cell%20and%20you%20will%20get%20workbook%20and%20sheet%20name.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20Excel%204%20Macro%20and%20not%20being%20supported.%20You%20can%20use%20it%20in%20Names%20though.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%20you%20can%20use%20below%20macro%2C%20and%20type%20%22%3DSayfaIsmiAl()%22%20in%20a%20cell%20to%20get%20sheet%20name%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EPublic%20Function%20SayfaIsmiAl(Optional%20ByVal%20No%20As%20Integer)%20As%20Variant%3C%2FP%3E%3CP%3EApplication.Volatile%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20No%20%26gt%3B%20ActiveWorkbook.Worksheets.Count%20Then%3CBR%20%2F%3E%3CBR%20%2F%3ESayfaIsmiAl%20%3D%20CVErr(xlErrRef)%3CBR%20%2F%3EExit%20Function%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20SayfaIsimleri()%20As%20String%3CBR%20%2F%3EReDim%20SayfaIsimleri(1%20To%201)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20SayfaNo%20As%20Integer%3CBR%20%2F%3EFor%20SayfaNo%20%3D%201%20To%20ActiveWorkbook.Worksheets.Count%3CBR%20%2F%3E%3CBR%20%2F%3EReDim%20Preserve%20SayfaIsimleri(1%20To%20SayfaNo)%3CBR%20%2F%3ESayfaIsimleri(SayfaNo)%20%3D%20ActiveWorkbook.Worksheets.Item(SayfaNo).Name%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20SayfaNo%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20No%20%3D%200%20Then%3CBR%20%2F%3ESayfaIsmiAl%20%3D%20SayfaIsimleri%3CBR%20%2F%3EElse%3CBR%20%2F%3E%3CBR%20%2F%3ESayfaIsmiAl%20%3D%20SayfaIsimleri(No)%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1682598%22%20slang%3D%22en-US%22%3ERe%3A%20workbook%20and%20sheet%20name%20via%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571483%22%20target%3D%22_blank%22%3E%40liverlarson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfraid%20for%20Excel%20Online%20that's%20only%20with%20Office%20Script%2C%20there%20are%20properties%20getName%20both%20for%20workbook%20and%20worksheet.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVBA%20and%20related%20functions%20don't%20work%20in%20Excel%20Online.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1683158%22%20slang%3D%22de-DE%22%3ESubject%3A%20workbook%20and%20sheet%20name%20via%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1683158%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F571483%22%20target%3D%22_blank%22%3E%40liverlarson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ERead%20out%20the%20designation%20%2F%20name%20of%20the%20worksheet%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3ERead%20out%20by%20formula%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20The%20focus%20of%20the%20formula%20solution%20is%20the%20CELL%20function%20(infotype%3B%20reference).%20In%20addition%20to%20the%20file%20name%20and%20the%20full%20path%2C%20the%20File%20Name%20infotype%20also%20returns%20the%20name%20of%20the%20worksheet.%20This%20is%20extracted%20using%20further%20functions%20so%20that%20only%20the%20sheet%20name%20is%20output%20at%20the%20end.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20The%20result%20is%20achieved%20with%20these%20two%20formulas%3A%20%3CBR%20%2F%3E%20(formulas%20are%20from%20German%20Translate)%20%3CBR%20%2F%3E%20a)%20%3D%20PART%20(CELL%20(%22filename%22%3B%20A1)%3B%20FIND%20(%22%5D%22%3B%20CELL%20(%22filename%22%3B%20A1))%20%2B%201%3B%20255)%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20or%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20b)%20%3D%20PART%20(CELL%20(%22filename%22%3B%20A1)%3B%20FIND%20(%22%5D%22%3B%20CELL%20(%22filename%22%3B%20A1))%20%2B%201%3B%20LENGTH%20(CELL%20(%22filename%22%3B%20A1))%20-%20FIND%20(%22%5D%22%3B%20CELL%20(%22filename%22%3B%20A1)))%20%3CBR%20%2F%3E%3CBR%20%2F%3E%202.%20Read%20out%20via%20VBA%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20The%20following%20VBA%20function%20reads%20out%20the%20sheet%20name%20via%20VBA%20and%20displays%20it%20in%20a%20cell.%20To%20do%20this%2C%20enter%20the%20following%20code%20in%20a%20code%20module.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPublic%20Function%20sheetname%20()%20As%20String%0AApplication.Volatile%0Asheetname%20%3D%20ActiveSheet.Name%0AEnd%20function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CBR%20%2F%3EIf%20you%20now%20enter%20the%20function%20%3D%20sheetname%20()%20in%20a%20cell%2C%20the%20sheet%20name%20is%20read%20out%20and%20output%20in%20the%20corresponding%20cell.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1683432%22%20slang%3D%22en-US%22%3EBetreff%3A%20workbook%20and%20sheet%20name%20via%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1683432%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%2C%20VBA%20doesn't%20work%20with%20Excel%20Online%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

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 via CELL("Filename",A1) formula. 

 

For Excel workbooks that are being viewed in Excel Online, however, whether through onedrive, sharepoint, or whatever, this formula yields a #VALUE! error. When I open in desktop, it renders fine, just not when looking at browser window. 

 

Is there an alternative that I can use to display current workbook and worksheet names via a formula in a cell that is compatible with Excel Online?

10 Replies
Highlighted

@liverlarson 

you need to create a Name like "SheetName" and use GET.CELL(32,A1) in the Refers To area. Whenever you need the sheet name you need to type "=SheetName" in the cell and you will get workbook and sheet name. 

 

This is a Excel 4 Macro and not being supported. You can use it in Names though.

Highlighted
Best Response confirmed by liverlarson (Contributor)
Solution

@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.

Highlighted

@liverlarson 

 

Read out the designation / name of the worksheet

Read out by formula

The focus of the formula solution is the CELL function (infotype; reference). In addition to the file name and the full path, the File Name infotype also returns the name of the worksheet. This is extracted using further functions so that only the sheet name is output at the end.

The result is achieved with these two formulas:
(formulas are from German Translate)
a) = PART (CELL ("filename"; A1); FIND ("]"; CELL ("filename"; A1)) + 1; 255)

or

b) = PART (CELL ("filename"; A1); FIND ("]"; CELL ("filename"; A1)) + 1; LENGTH (CELL ("filename"; A1)) - FIND ("]"; CELL ("filename"; A1)))

2. Read out via VBA

The following VBA function reads out the sheet name via VBA and displays it in a cell. To do this, enter the following code in a code module.

 

Public Function sheetname () As String
Application.Volatile
sheetname = ActiveSheet.Name
End function

 


If you now enter the function = sheetname () in a cell, the sheet name is read out and output in the corresponding cell.

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Highlighted

@Nikolino , VBA doesn't work with Excel Online

Highlighted
You are right, I disregarded the labels
Highlighted

@Sergei Baklan Hmm, wasn't aware of office scripts. From my 30 seconds of research it looks like they are 1) pretty new, 2) basically VBA/macros for online

 

If I did an office script to accomplish this functionality, does that in any way translate to the workbook when I download it?

 

Such a simple thing. Seems weird to have to use an online-specific solution. As an aside, do you happen to know why CELL doesn't work online?

Highlighted

@liverlarson 

 

Does Office 365 Excel support VBA?

 

All versions of Windows desktop Excel from Excel 5 through Excel 2019 support VBA.

This includes the version of Excel installed by Office 365. ...

There is no version of Excel Online, iOS Excel or Android Excel that supports VBA.

Nor does any version of Excel that runs on Windows RT support VBA.

 

In my humble opinion, it would be good to always have the possibility of VBA in Excel.

All new options such as editing PDF and cloud options, in all future versions, will be require VBA.

Unless someone has already adjusted to Java script ... which I don't hope.

 

What I don't know is, if you maybe can open from the Developer tab in Visual Basic editor, I don't have Office 365 yet. You can try it, maybe…maybe it will work.

The sequence is in Excel 2016

     Click File

     Click Options

     Click Customize Ribbon

     Under the list of Main Tabs, select Developer

     Click OK

The Developer tab will now appear on the Ribbon and from it you can open the Visual Basic Editor.

 If opening the Developer tab and clicking a button is a little too much work, you can also open the editor with the keyboard shortcut Alt+F11, which works whether the Developer tab is enabled or not.

As I told you before, with the logic of Excel 2016.

 

Nikolino

I know I don't know anything (Socrates)

Highlighted

@liverlarson 

You are right, Office Script is relatively new functionality available only for targeted tenants, that means not for everyone so far. And yes, basically they have the same role as VBA/macro for desktop versions, but that's totally different technology. Since the engines which runs online and desktop versions are different.

 

Back to question, Excel Online has limited functionality compare to desktop version which, however, is expanded quite fast. To my knowledge there is no built-in function which allows to return file/sheet name. 

Highlighted
yeah, VBA is supported in O365 - this question is specific to Excel documents being intereacted with through a web browser (Excel online) though.

The CELL function which works on desktop does not work with online (weirdly), and same is true of VBA.

It's a pretty small detail (CELL formula not working online), not a critical functionality, just annoying to me, because I literally use the same header on every document as a matter of best practice.
Highlighted

@liverlarson 

- you may always check if function available for online version or not here Excel functions (alphabetical) In particular,  what it says for CELL

image.png

- you may vote for the idea to add CELL() for the online version here CELL function added to online . In theory more votes more chances it'll be added.  

- Perhaps one day functionality of desktop and online versions will be synced, at least that's how I understand Microsoft intention. But with that most probably not VBA will go online but Office Script will go on desktop. That's a long way.