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?

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.

workbook and sheet name via formula | Microsoft Community Hub

62 Replies

Jan_Wijninckx
Brass Contributor
Feb 26, 2024
As stated before that doesn't work in the Web / SharePoint version 🙂
noSheetMaster
Copper Contributor
Feb 20, 2024
liverlarson
Hi, I don't know if you ever solved your problem. I hope you did, but in case you didn't, this was my jurney to solve it.

I have now a working sollution for both web and non web Excel spreadsheets, unfortunatly the Formula it's in Portuguese. Most probably you won't understand it, but I put it here anyway. Maybe from one you get the other.

The basic thing you need to understand is that, the same formula in Excel offline and in excel online gives different result's. For this porpous A1 = "=Sheet1!A1"
The "FORMULATEXT(A1)" formula offline result in ='Sheet1'!A1 and online result in =Sheet1!A1 (exactly), so if you try to look for "'" online, it will give an error, but not offline, so this is usefull to use IFERROR() function, wish solved both problems.

So, in general, offline you look for "'" and online you look for "!" (both are arround of what you want to know), first I looked for the Left Text of those, and after I took the right text except 1 or 2 chars.

My Formula (in portuguese excel) is this one:

=SE.ERRO(DIREITA(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1);NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1))-2);DIREITA(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1);NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1))-1))

Trying to decompress it looks like this:
=SE.ERRO( #first part of error will work offline but not online
DIREITA(
ESQUERDA(
FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1
) #end of left
;
NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1))-2
) #end of right
; midle of iferror (second part work online)
DIREITA(
ESQUERDA(
FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1
) #end of left
;
NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1))-1
) #end of right
) #end of iferror

Translation of formulas probably looks like this (not sure..)
all ; must change to ,
SE.ERRO = IFERROR
DIREITA = RIGHT
ESQUERDA = LEFT
FÓRMULA.TEXTO = FORMULATEXT
NÚM.CARAT = LENGHT
PROCURAR = FIND
(the main diference are the number of arguments that each function has in both languages, so you need to account for that.)

Copilot gave me this translation result:

To translate the Portuguese Excel formula to English, you can use the following formula:
=IFERROR(RIGHT(LEFT(FORMULATEXT(A1),FIND("'",FORMULATEXT(A1),3)-1),LEN(LEFT(FORMULATEXT(A1),FIND("'",FORMULATEXT(A1),3)-1))-2),RIGHT(LEFT(FORMULATEXT(A1),FIND("!",FORMULATEXT(A1),3)-1),LEN(LEFT(FORMULATEXT(A1),FIND("!",FORMULATEXT(A1),3)-1))-1))
This formula performs the same operations as the original Portuguese formula. It extracts the relevant portion of the formula based on the positions of single quotes (') and exclamation marks (!) within the text contained in cell A1.
Please note that this is an approximation, and it may not work correctly if the formula structure is different or if there are other special characters involved.

Hope it works for you! and thanks for reading untill here 🙂
- MikeFromCanada
  Copper Contributor
  Feb 20, 2024
  noSheetMaster
  
  Looking for single quotes won't work in all cases. I found out just last week that Excel only requires the single quotes for sheets with spaces or that begin with numbers.
  
  Best I've been able to do is:
  IFERROR( [formula that works other than Excel Web App] ,
  "Formula doesn't work in Excel for the Web").
  
  Admittedly it doesn't solve the Web problem, but at least tells the user what's going on...
  
  Addendum: REALLY impressed with your response generally, let alone that you translated it into a second language.
  - Jan_Wijninckx
    Brass Contributor
    Feb 26, 2024
    The best solution which has been listed high above y someone else remains:
    =SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D1), "!"), "'", "" ), "=", "")
    With D1 containing e.g.:
    ='3 Sheet 3'!D2
    This works for sheet names like "3 Sheet" or "Sheet 3" or "Sheet3" And resolves the single quotes or not single quotes.
    Very simple, very elegant
johnodirectionz
Copper Contributor
Apr 24, 2023
Just in case anyone else was after a formula using standard excel functions to copy paste and get the sheet name: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))+1)
- Patrick2788
  Silver Contributor
  Jul 22, 2023
  johnodirectionz
  An alternative to fetch sheet name (365, desktop version only):
  =TEXTAFTER(CELL("filename"),"]")
  - Jan_Wijninckx
    Brass Contributor
    Jul 23, 2023
    Wow Patrick, very elegant. However that doesn't work in Excel in SharePoint.
- SergeiBaklan
  MVP
  Apr 25, 2023
  johnodirectionz
  
  This thread mainly about Excel for web where such formula doesn't work.
GeertCL
Copper Contributor
Mar 10, 2023
With Excel 365, this can be beautified by creating a defined name in workbook scope:
Name:
GetSheetName
Refers to:
=LAMBDA(refCell; LET(frmTxt; FORMULATEXT( INDIRECT( ADDRESS(ROW();COLUMN()) ) ); SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt;"!"); "("; -1); "'"; "") ))
Then, you can use a simplified form in your worksheets:
=GetSheetName(Sheet1!A1)
Again, you have to add the name of the sheet in the cell reference manually if you want to retrieve the name of the same worksheet. After that, it is automatically updated if you rename or duplicate the worksheet.
- Guy_Boswell
  Brass Contributor
  Mar 10, 2023
  GeertCL that only returns 0
  - GeertCL
    Copper Contributor
    Mar 10, 2023
    Guy_Boswell
    Hi Guy,
    
    There must be something specific about my Excel settings that differs in your case.
    As you can see in the above screenshot, I have the expected results on the web browser-based version of Excel Online, both for the name of the same worksheet as for another worksheet.
    However, I did notice that one cannot create the defined name in the web browser, only use it. You have to open it with the Excel app to have access to the Name Manager.
    Also, in my localisation, the separator is a semi-colon, not a comma.
    
    Unfortunately, some testing and debugging will be necessary in your case.
    Row 4 shows how you can use the LAMBDA function directly, without defined name: note that you have to add the ('Sheet1 Copy'!A1) argument at the end.
    =LAMBDA(refCell, LET(frmTxt, FORMULATEXT( INDIRECT( ADDRESS(ROW(),COLUMN()) ) ), SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt,"!"), "(", -1), "'", "") ))('Sheet1 Copy'!A1)
    As the result shows, this is fooled by the preceding exclamation mark that appears in the TEXTBEFORE function.
    For testing, one can adapt the TEXTBEFORE function to search for the exclamation mark in reverse direction with the -1 value in the third argument (row 5):
    =LAMBDA(refCell, LET(frmTxt, FORMULATEXT( INDIRECT( ADDRESS(ROW(),COLUMN()) ) ), SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt,"!",-1), "(", -1), "'", "") ))('Sheet1 Copy'!A1)
GeertCL
Copper Contributor
Mar 10, 2023
Actually, one can do it without VBA or hidden sheets using the following formula:

=IF(FALSE;'Sheet1'!A1;MID(FORMULATEXT(INDIRECT(ADDRESS(ROW();COLUMN())));11;FIND("!";FORMULATEXT(INDIRECT(ADDRESS(ROW();COLUMN()))))-11))
In this formula, you replace the 'Sheet1'!A1 by the reference to the first cell in the sheet whose name you want to show. It can be the current sheet, but then you have to add the sheet name once, manually. If you are lazy like me, you can also enter this formula temporarily on another sheet, use point-and-click to the current sheet to insert the reference, and then copy the formula over.

The formula exploits the fact that it uses its own text, and that the prefix is known ("=IF(FALSE;" - 11 characters). That is where the ADDRESS/ROW/COLUMN construct comes in: that is how the FORMULATEXT gets the formula of the current cell, while referencing another cell.

If you rename the sheet, or copy it as a duplicate sheet, the reference updates automatically.
This is the basic version of the formula. The result will still contain the single quotes if the sheet name contains blanks. You can surround the formula with a SUBSTITUTE(formula;"'";"") construct to get rid of those.
In Excel 365, this can be simplified with the TEXTBEFORE and LET functions, and you can create a GetSheetName(reference) function-like defined name that uses the LAMBDA dynamic function.
- MikeFromCanada
  Copper Contributor
  Feb 13, 2024
  GeertCL
  Hoping for your help:
  Your first formula works in Excel 365 on the web or the desktop app (you used semicolons/ I've restated for the North American comma convention):
  =IF(FALSE, 'Sheet1'!A1, MID(FORMULATEXT(INDIRECT(ADDRESS(ROW(), COLUMN()))), 11, FIND("!", FORMULATEXT(INDIRECT(ADDRESS(ROW(), COLUMN()))))-11))
  returns
  '100_Core_CY'
  You'll note that I replaced the blanks in my sheet name with underscores, yet your formula still returns the single quotes on either side of the sheet name.
  I tried to remove them using your SUBSTITUTE suggestion, but that didn't work. It appears to interfere with the FALSE command:
  =SUBSTITUTE( IF(FALSE,'100_Core_CY'!$A$1,MID(FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))),11,FIND("!",FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))))-11)) ,"'","") [that last bit is comma, double-quote, single-quote, double-quote, then replace with two double-quotes, i.e. null.]
  returns
  E(IF(FALSE,100_Core_CY
  
  I'd be grateful if you could take a moment to explain where I might have gone wrong.
  
  Many thanks!
  - Jan_Wijninckx
    Brass Contributor
    Feb 14, 2024
    Dear MikeFromCanada , you didn't address this to me so I'll happily butt out; still, why are you trying to make it so complicated?
    Perhaps you wish to use a Lambda function to only have one parameter?
    This is the shortest function that just works:
    =MID(CELL("filename",D1),FIND("]",CELL("filename",D1))+1,32)
    where d1 contains a reference like =Sheet1!D2, with formatting set to 0;0; The easiest to create that reference is to type (in D1) = , select another sheet, then select Sheet1 again and click on D2.
    The trick to avoid issues and improve maintainability is to keep it simple?!
    Before you create the Lamba, put your formula in a cell, then use the Formulas > Evaluate formula function to step through what is going wrong. Once debugged, turn it into a Lambda
- pblommel
  Copper Contributor
  Oct 05, 2023
  The solution of GeertCL was the most workable for me?
Nick_from_London
Copper Contributor
Jan 17, 2023
liverlarson
Thanks to the help above the following formula worked for me.
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
CELL("filename",A1) returns the full path filename and worksheet name with the work book in square brackets []
LEN (string) gives the length of a string
FIND (string1, string2)returns the position from the left of the first occurrence of string1 in string2.
RIGHT(string ,n) gives the last n characters counting from the right.
- asherin
  Copper Contributor
  Apr 15, 2023
  Nick_from_London
  
  Hi Everyone,
  
  My workaround for returning the worksheet cell/range reference in Excel Online is as follows:
  
  Pick an input cell where you will reference a cell or range. In this case it is D2.
  In another cell (in this example I use A2), paste the following formula:
  =REPLACE(FORMULATEXT(D2),1,1,"")
  This formula turns the formula in D2 into a text string and then replaces the equals sign in the first character with "", removing it from the string.
  
  In D2, type the equals sign, then use your cursor to select the cell or range you need the reference for and hit return. A2 should now display the reference of the range/cell added to D2.
  This works across sheets and probably across workbooks with the correct permissions.
  
  I am personally using it as a way to expedite modification of a 3rd formula.
  In this 3rd formula, I am replacing range/cell references with a reference to what appears in D2, but enclosed in the Indirect function:
  Indirect(D2)
  This will take the string in D2 and treat it as a cell reference again.
  Now I am able to replace the cell/range references in the formula by inputting them into other cells rather than changing the formula itself.
Guy_Boswell
Brass Contributor
Jul 19, 2021
Strange thing is that it works momentarily when you open the spreadsheet, displaying the correct value, before reverting to #VALUE!
- SergeiBaklan
  MVP
  Jul 19, 2021
  Guy_Boswell
  
  If you had some value in last saved in desktop version file, it is shown in Online version from cache till you recalculate worksheet. After that an error.
NikolinoDE
Gold Contributor
Sep 18, 2020
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)
- liverlarson
  Brass Contributor
  Sep 18, 2020
  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.
  - SergeiBaklan
    MVP
    Sep 19, 2020
    liverlarson
    
    - you may always check if function available for online version or not here Excel functions (alphabetical) In particular, what it says for CELL
    
    - 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.
NikolinoDE
Gold Contributor
Sep 18, 2020
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.
- 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
    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.
- NuggetSA
  Copper Contributor
  Jul 18, 2023
  NikolinoDE
  I guess the German translation wasn't too good. Try this:
  
  =MID(CELL("filename",'Sheet1'!A2),FIND("]",CELL("filename",'Sheet1!A2))+1,99)
  
  The MID() formula picks an extract from CELL("filename") starting at the FIND() position for the "]" and returning whatever is to the right. If you look at what =CELL('filename",<location>) returns it will help visualise.
  
  The 99 at the end is a lazy parameter assuming there is no more than 99 characters after the "]" was found. It's probably safe but you can increase if you want to or make the formula more complicated by calculating the remaining length. I don't think the additional technical artistry helps understand the formula!
  - Jan_Wijninckx
    Brass Contributor
    Jan 10, 2024
    NuggetSA - nice simplification, 99 can be reduced to 32 as that is the maximum length of a sheet name. But this only works in the desktop version not the SharePoint or Web instance.
- HughBT
  Copper Contributor
  Nov 21, 2020
  NikolinoDE Before moving to Office 365 I used this function to include the workbook name in the Heading of the Workbook (not in the header) so new versions automatically pick up the right name. It almost works in 365, using =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-6) but as soon as I open a 2nd workbook it changes the content of the cell in the 1st workbook to the filename of the newly opened workbook. Very frustrating and confusing!
  - SergeiBaklan
    MVP
    Nov 21, 2020
    HughBT
    
    You may vote here CELL function added to online for this function. More votes sooner it could appear.
SergeiBaklan
MVP
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.
- liverlarson
  Brass Contributor
  Sep 18, 2020
  SergeiBaklan 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?
  - SergeiBaklan
    MVP
    Sep 18, 2020
    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.

=IFERROR(RIGHT(LEFT(FORMULATEXT(A1),FIND(\"'\",FORMULATEXT(A1),3)-1),LEN(LEFT(FORMULATEXT(A1),FIND(\"'\",FORMULATEXT(A1),3)-1))-2),RIGHT(LEFT(FORMULATEXT(A1),FIND(\"!\",FORMULATEXT(A1),3)-1),LEN(LEFT(FORMULATEXT(A1),FIND(\"!\",FORMULATEXT(A1),3)-1))-1))

Forum Discussion

workbook and sheet name via formula

62 Replies

Resources